SQL 如何在另一个存储过程中使用一个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2996277/
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
How to use a value from one stored procedure in another?
提问by RoguePlanetoid
I have the following statement in a Stored Procedure:
我在存储过程中有以下语句：
DECLARE @Count INT
EXEC @Count = GetItemCount 123
SELECT @Count
Which calls another stored procedure with the following statement inside:
它调用另一个存储过程，其中包含以下语句：
SELECT COUNT(Item) FROM tblItem WHERE ID = @ID
However when I test the call the EXEC outputs the value correctly but it is not assigned to the @Count Variable correctly. I've seen examples or stored procedures used like this, including here but none had a parameter and a return value used (that I could find). The ID Parameter is passed into the second statement which returns a count value used by the first StoredProcedure - all the info I have read seems to indicate this should work - but it doesn't the @Count value is aways zero, even when the GetItemCount returns always the correct value.
但是，当我测试调用时，EXEC 正确输出值，但未正确分配给 @Count 变量。我见过这样使用的示例或存储过程，包括此处，但没有使用参数和返回值（我可以找到）。ID 参数被传递到第二个语句中，该语句返回第一个 StoredProcedure 使用的计数值——我读过的所有信息似乎都表明这应该有效——但@Count 值并不为零，即使 GetItemCount始终返回正确的值。
This is in Microsoft SQL Server 2008 if that helps.
如果有帮助，这在 Microsoft SQL Server 2008 中。
回答by Matthew Abbott
In your stored procedure, are you either
在您的存储过程中，您是
a) Assigning the value of the count to an output parameter:
a) 将计数值分配给输出参数：
CREATE PROCEDURE GetItemCount
@id INT,
@count INT OUTPUT
AS
SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @id
called as:
称为：
DECLARE @count INT
EXEC GetItemCount 123, @count OUTPUT
or, b) Assigning the count value as the return value:
或者，b) 将计数值分配为返回值：
CREATE PROCEDURE GetItemCount
@id INT
AS
BEGIN
DECLARE @count INT
SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @id
RETURN @count
END
called as:
称为：
DECLARE @count INT
EXEC @count = GetItemCount 123
回答by Madhivanan
Another way
其它的办法
DECLARE @Count table(counting INT)
Insert into @Count
EXEC GetItemCount 123
SELECT Counting FROM @Count
回答by MC9000
The following will not work if there are no input parameters for the executed SP:
如果执行的 SP 没有输入参数，以下将不起作用：
EXEC [dbo].insertproduc 'TEST', @ProductID OUTPUT --works
EXEC [dbo].insertproduc, @ProductID OUTPUT -- generates a parameter supplied when not needed error message if the insertproduc does not require parameters.
Best to use the following format for all cases (it works on all versions):
最好对所有情况使用以下格式（它适用于所有版本）：
DECLARE @MyOutputVariable int
EXEC @MyOutputVariable = [dbo].MyStoredProc
回答by codingbadger
You should pass @Countas an output parameter.
您应该@Count作为输出参数传递。
Create Proc dbo.usp_Proc1
@Id int,
@Count int output
as begin
select @Count = Count(Item) from tblItem where id=@Id
end
Go
Declare @Count int
Declare @Id int
Set @Id = 1
Exec dbo.usp_Proc1 @Id, @Count output
select @Count
回答by santhosh kumar Gudise
The solution (b) given by Mr. Matthew will not work when u call this in an another stored procedure (Ofcourse solution (a) works perfectly when we use OUTPUT param). Alternation for the solution (b) is Mr.Madhivanan's solution. i.e, create a temp table and use it then drop it.
当您在另一个存储过程中调用它时，Matthew 先生给出的解决方案 (b) 将不起作用（当然，当我们使用 OUTPUT 参数时，解决方案 (a) 工作得很好）。解决方案 (b) 的替代方案是 Mr.Madhivanan 的解决方案。即，创建一个临时表并使用它然后删除它。
Below are other solutions..
以下是其他解决方案..
We cannot get the value from an internal OUTPUT clause in a stored procedure directly. So we have to use OUTPUT parameter or RETURN VALUE instead.
我们无法直接从存储过程中的内部 OUTPUT 子句获取值。所以我们必须使用 OUTPUT 参数或 RETURN VALUE 来代替。
Please refer to the following suggestions:
请参考以下建议：
SOLUTION 1:
解决方案1：
CREATE PROCEDURE [dbo].[InsertProduct]
@pName varchar(50)
AS
BEGIN
DECLARE @MyTableVar Table(ProductID
int)
INSERT Products
(
pName
)
OUTPUT Inserted.ProductID
INTO @MyTableVar
VALUES
(
@pName
)
RETURN (SELECT ProductID
FROM @MyTableVar)
END
DECLARE @ProductID int
EXEC @ProductID = [dbo].insertproduc 'TEST'
SELECT @ProductID
SOLUTION:2
解决方案：2
CREATE PROCEDURE [dbo].[InsertProduct]
@pName varchar(50) , @pID int output
AS
BEGIN
DECLARE @MyTableVar Table(ProductID int)
INSERT Products
(
pName
)
OUTPUT Inserted.ProductID INTO @MyTableVar
VALUES
(
@pName
)
SELECT @pID=ProductID FROM @MyTableVar
END
DECLARE @ProductID int
EXEC [dbo].insertproduc 'TEST', @ProductID OUTPUT
SELECT @ProductID
回答by Mohan Gundlapalli
What you are catching in the variable @Count is the status of execution of the stored procedure GetItemCount and not the value as you are expecting. To capture that value, you have two ways.
您在变量 @Count 中捕获的是存储过程 GetItemCount 的执行状态，而不是您期望的值。要获取该价值，您有两种方法。
To declare another parameter @count as OUTPUT in GetItemCount. So you will have GetItemCount declaration as follows
CREATE PROCEDURE GetItemCount @ID int, @count int output
在 GetItemCount 中将另一个参数 @count 声明为 OUTPUT。所以你将有 GetItemCount 声明如下
CREATE PROCEDURE GetItemCount @ID int, @count int 输出
inside, you can use
在里面，你可以使用
SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @ID
SELECT @count = COUNT(Item) FROM tblItem WHERE ID = @ID
- To declare a table before calling the proc and getting that value as a column.
- 在调用 proc 并将该值作为列获取之前声明一个表。
