Assign the results of a stored procedure into a variable in another stored procedure

Question

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.

Answer 1

To get the return value you would need to do

DECLARE @RC int
EXEC  @RC = s_proc2 @SiteID, @count = @PagingCount OUTPUT

Also should your dynamic SQL be

'SELECT @xCount = COUNT(ID) FROM Authors'

I can't see @xCount being assigned to anywhere.

Answer 2

try this

SELECT @sSQL = 'SELECT @xCount = COUNT(ID) FROM Authors' 
EXEC sp_ExecuteSQL @sSQL, N'@xCount int output', @count = @xCount output