SQL Server Stored Procedure store return value
Solution 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
Solution 2
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 ...
Solution 3
Have you tried changing
SET @SQL = "SELECT COUNT(*) FROM blah"
EXEC(@SQL)
to
SELECT @variable = COUNT(*) FROM blah"
-- don't do EXEC(@SQL)
?
Related videos on Youtube
Comments
-
Phil almost 2 years
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.
-
Mladen Prajdic over 14 yearsyou'll have to show us more code since your question isn't clear.
-