SQL Server Stored Procedure store return value

Question

SQL Server Stored Procedure store return value

Đặng Hạnh Nga · 15:46 27/08/2009

3 giờ trước

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.

9 hữu ích 3 bình luận 48k xem chia sẻ

4 trả lời

Viết câu trả lời

Xem nguồn

Đăng nhập để trả lời câu hỏi

Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ sql sql server sql-server-2005 sql server 2008 stored-procedures , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm

Hoạt động

Liên quan

Mới tạo

Đỗ Việt Thanh · Đỗ Việt Thanh 15:59 27/08/2009 15:59:55 27/08/2009 · Answer 1 · 8/27/2009 3:59:55 PM

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

score 4 · Answer 2 · 8/27/2009 3:54:59 PM

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 ...

Phan Vĩnh Đức · Phan Vĩnh Đức 15:30 10/09/2010 15:30:25 10/09/2010 · Answer 3 · 9/10/2010 3:30:25 PM

Have you tried changing

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

to

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

?

Huỳnh Gia Uy · Huỳnh Gia Uy 11:41 18/11/2015 11:41:35 18/11/2015 · Answer 4 · 11/18/2015 11:41:35 AM

THE FIRST PROCEDURE:
CREATE PROC DD43
@ID INT OUTPUT AS
(SELECT @ID=COUNT(*) FROM CS2)

SECOND PROCEDURE:

 CREATE PROC DD45 AS
 DECLARE @COUNT INT
 DECLARE @COUN INT
 EXEC DD43 @COUN OUT --CALLING THE FIRST PROCEDURE
 SET @COUNT= (SELECT @COUN)
 SELECT @COUNT

 EXEC DD45