December 2, 2009 at 2:19 pm
I know this should probably be easy but I've tried several syntaxes and none of them work. I basically need to call a stored procedure from inside another stored procedure and put a single returned value into a variable:
Declare @Result int
SET @Result = exec sp_myprocedure @Param1, @Param2, @Param3
Does anyone know the syntax for what I'm trying to do here?
December 2, 2009 at 2:24 pm
Declare @Result int
exec @Result = sp_myprocedure @Param1, @Param2, @Param3
Aside, don't name stored procedures sp_<whatever>. sp_ means system procedure and, if a proc is named that, resolution is first to the system resource database, then to the local database. Only the built-in system procedures should be named sp_<name>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 2:50 pm
I tried that code, its actually executing the procedure and returning it as a result from the
calling stored procedure. I need it to just put the value into @Result and do nothing else.
December 2, 2009 at 3:07 pm
tnocella (12/2/2009)
I tried that code, its actually executing the procedure and returning it as a result from thecalling stored procedure.
Well, yes. That's what you want isn't it?
I basically need to call a stored procedure from inside another stored procedure and put a single returned value into a variable:
I need it to just put the value into @Result and do nothing else.
What value?
Post the code for the procedure that you're calling please and explain in a little more detail what it is that you're trying to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 3:24 pm
I'll try to explain in better detail:
Create Procedure p_myproc1
Begin
SELECT 1
End
--- Proc 1 just returns a value of 1 through a select
Create Procedure p_myproc2
Begin
Declare @Result int
SET @Result = 0
Exec @Result = p_myproc
SELECT 9,@Result
END
proc 2 calls proc one, but its returning 2 result sets, and not even storing the value. The above procedure 2 returns these 2 result sets:
1
9 0
( it returns the value from the execution of myproc1, and then returns the select statement also - a 9 and @Results orginal value ( @Result does not get properly set ).
December 2, 2009 at 4:16 pm
Exec @Result = p_myproc1
running this code executes the SP p_myproc1 and gets the status of the SP p_myproc1 in the @Result. 0 means p_myproc1 executed successfully. If you want to get a value from a SP you need to "return" that value.
Try this:
Create Procedure p_myproc1
AS
Begin
DECLARE @ReturnValue int
SELECT @ReturnValue = 1 /*Replace 1 with your select statement*/
RETURN @ReturnValue
End
GO
Create Procedure p_myproc2
as
Begin
Declare @Result int
SET @Result = 0
EXEC @Result = p_myproc1
SELECT 9,@Result
END
GO
EXEC p_myproc2
One other option is to use Output Parameters.
Does this make sense?
-Supriya
December 3, 2009 at 1:22 am
tnocella (12/2/2009)
Create Procedure p_myproc1Begin
SELECT 1
End
--- Proc 1 just returns a value of 1 through a select
Create Procedure p_myproc2
Begin
Declare @Result int
SET @Result = 0
Exec @Result = p_myproc
SELECT 9,@Result
END
When you say, in the inner proc SELECT 1, you're saying 'return the value 1 in a resultset'. If you want the value 1 returned as the execution status, it should read RETURN 1, not SELECT 1
This will do what you're asking, though I'm still not sure if it's the appropriate way of doing this.
Create Procedure p_myproc1
Begin
RETURN 1
End
Create Procedure p_myproc2
Begin
Declare @Result int
SET @Result = 0
Exec @Result = p_myproc
SELECT 9,@Result
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply