Given a call to a stored procedure like this:
declare @return_value int
exec @return_value = dbo.myProcedure
How do I re-assign @return_value to e.g. an attribute or a variable?
Question
SQLExecutor with Exec - how to use the return value?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+26- Contributor
Why not use:
Select dbo.myProcedure as return_value from table;
In the SQLExecutor?
In Oracle that table would be Dual, Not sure if SQL Server has a 1 record dummy table too.
+6- Safer
Hello @michaeljb
I believe in order to return a value from a Stored Procedure you would need the following:
1. Needs to ensure the procedure has return values or OUT parameters.
2. expose the attributes you are expecting on the SQLExecutor using the Attributes to Expose setting. You can type in the name of the value to be returned.
A similar question can be seen here, aside from using an Oracle database: https://knowledge.safe.com/questions/4782/results-from-oracle-stored-procedure.html
Hmm... I don't think you can use a select on a stored procedure? Sorry, didn't mention that in the question....
Why not use:
Select dbo.myProcedure as return_value from table;
In the SQLExecutor?
In Oracle that table would be Dual, Not sure if SQL Server has a 1 record dummy table too.
Hello @michaeljb
I believe in order to return a value from a Stored Procedure you would need the following:
1. Needs to ensure the procedure has return values or OUT parameters.
2. expose the attributes you are expecting on the SQLExecutor using the Attributes to Expose setting. You can type in the name of the value to be returned.
A similar question can be seen here, aside from using an Oracle database: https://knowledge.safe.com/questions/4782/results-from-oracle-stored-procedure.html
Where _returnValue would be an attribute. I'd be happy if I could feed it into a variable, too
@trentatsafe With Exec, it appears it's the initiator outlet only, but the attribute that is exposed is only available from result, which is not used....
+26- Contributor
dbo.myProcedure is a stored procedure, I don't think select works for that....
If it is a stored function both the return_value = and the SQL will work.
If it is a stored procedure, the return_vale = stored_procedure will not work either.
If it is a stored function both the return_value = and the SQL will work.
Procedures don't have a return value, only Functions do?
And that this flows back into FME?
+1- Contributor
Well, a little late in answering this, but hopefully it might be useful to someone along the way.
I do the following when running MS SQL Server stored procedures:
DECLARE @return_value int
EXEC @return_value=DBO.A_TEST
SELECT @return_value as X
This results in X having a value of zero when the procedure executes without any errors. If there is an error in your code or within the stored procedure then it will show X with a value of NULL under "<Rejected>".
Also, if you want different results depending on what happens in the procedure then you can use a variable in your procedure and return that as your last value.
Example:
ALTER PROCEDURE dbo.A_TEST
AS
BEGIN
DECLARE @Return_Value INT;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @Return_Value = 1
-- Return the value as last statement of stored procedure.
RETURN @Return_Value
END
If I run the above code, it returns a 1.
