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?
+17- 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....
erik_jan wrote:
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.
trentatsafe wrote:
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....
+17- Contributor
michaeljb wrote:
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.
erik_jan wrote:
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?
- Participant
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.
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.