Assign the results of a stored procedure into a variable in another stored procedure
- by RHPT
The title of this question is a bit misleading, but I couldn't summarize this very well.
I have two stored procedures. The first stored procedure (s_proc1) calls a second stored procedure (s_proc2). I want to assign the value returned from s_proc2 to a variable in s_proc1. Currently, I'm calling s_proc2 (inside s_proc1) in this manner:
EXEC s_proc2 @SiteID, @count = @PagingCount OUTPUT
s_proc2 contains a dynamic query statement (for reasons I will not outline here).
CREATE dbo.s_proc2
(
@siteID int,
@count int OUTPUT
)
AS
DECLARE @sSQL nvarchar(100)
DECLARE @xCount int
SELECT @sSQL = 'SELECT COUNT(ID) FROM Authors'
EXEC sp_ExecuteSQL @sSQL, N'@xCount int output', @xCount output
SET @count = @xCount
RETURN @count
Will this result in @PagingCount having the value of @count? I ask because the result I am getting from s_proc1 is wonky. In fact, what I do get is two results. The first being @count, then the result of s_proc1 (which is incorrect). So, it makes me wonder if @PagingCount isn't being set properly.
Thank you.
