SQL Server 存储过程存储返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1341948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Stored Procedure store return value
提问by Phil
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.
我的问题是我在 SQL Server 中有一个返回字段计数的存储过程。我想将此存储过程的结果存储在不同存储过程的变量（标量？）中。
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.
然而，它返回其他存储过程结果以及主存储过程结果，这是我的 .NET 应用程序中的一个问题。
-----------
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.
谢谢你的帮助。
回答by Adam Hughes
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
回答by Charles Bretana
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
好吧，解决此问题的最简单方法是重新编码存储过程，以便在这种情况下返回您不想要的“其他”结果集的 select 语句有条件地执行，仅当您不要求计数时
Add another parameter called @GetCount
添加另一个名为@GetCount 的参数
@GetCount TinyInt Defualt = 0 // or
@GetCount Bit Default = 0
Then instead of just
然后而不是仅仅
Select ...
write
写
If @GetCount = 1
Select ...
回答by Jim Bell
Have you tried changing
你有没有试过改变
SET @SQL = "SELECT COUNT(*) FROM blah"
EXEC(@SQL)
to
到
SELECT @variable = COUNT(*) FROM blah"
-- don't do EXEC(@SQL)
?
?
回答by KEERTHANA
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
