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

Well, the easiest way to fix this is to recode the stored proc so that the select statement that returns the 'other' result set you don't want in this case is conditionally extecuted, only when you are NOT asking for the count

Add another parameter called @GetCount

@GetCount TinyInt Defualt = 0 // or
@GetCount Bit Default = 0

Then instead of just

Select ...

write

   If @GetCount = 1
     Select ...

Answer 2

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

Answer 3

Have you tried changing

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

to

SELECT @variable = COUNT(*) FROM blah" -- don't do EXEC(@SQL)

?