SQL Server Stored Procedure store return value

Question

Helo,

My question is I have one Stored Procedure in SQL Server that returns counts of a field. I want to store the results of this Stored Procedure in a variable (scalar?) of a different stored procedure.

sp_My_Other_SP:

CREATE PROCEDURE [dbo].sp_My_Other_SP
@variable int OUTPUT -- The returned count
AS

BEGIN -- SP

SET NOCOUNT ON;

SET @SQL = "SELECT COUNT(*) FROM blah"
EXEC(@SQL)

END -- SP

I currently do it like:

DECLARE @count int

EXEC sp_My_Other_SP @count OUTPUT

Then I use it like

IF (@count > 0)
BEGIN
...
END

However its returning the other Stored Procedure results as well as the main Stored Procedure results which is a problem in my .NET application.

-----------
NoColName
-----------
14

-----------
MyCol
-----------
abc
cde
efg

(Above is an attempted representation of the results sets returned)

I would like to know if there is a way to store the results of a Stored Procedure into a variable that doesn't also output it.

Thanks for any help.

Answer 1

You can capture the results of the stored procedure into a temp table so it is not returned by the calling stored procedure.

create table #temp (id int, val varchar(100))
insert into #temp
exec sp_My_Other_SP @value, @value, @value, @count OUTPUT