我知道这是一个基本问题 - 但我无法找到完成此任务的正确方法。
我需要将变量传递给 SQL Server 2008 存储过程并返回查询。
这是存储过程:
CREATE PROCEDURE pOrders
AS
DECLARE @enteredClientID varchar(20);
DECLARE @Results table (ClientID varchar(20), Num_orders int);
BEGIN
SET NOCOUNT ON;
-- Get all the order from the client
INSERT INTO @Results
SELECT ClientID, sum(Num_orders)
FROM Orders O
WHERE O.ClientID = @enteredClientID
GROUP BY O.ClientID);
-- Insert the orders into the results table --
SELECT *
FROM @Results;
END
GO;
现在,我将执行存储过程并获取结果:
exec pOrders
set @enteredClientID = 'ABC123456789'
我收到此错误消息:
必须声明标量变量“@enteredClientID”。
但是,我正在声明变量......我错过了什么?
您没有声明参数,而是声明局部变量。要按照您的意愿声明它:
CREATE PROCEDURE pOrders (@enteredClientID varchar(20))
AS
DECLARE @Results table (ClientID varchar(20), Num_orders int);
SET NOCOUNT ON;
-- Get all the order from the client
INSERT INTO @Results
SELECT ClientID, sum(Num_orders)
FROM Orders O
WHERE O.ClientID = @enteredClientID
GROUP BY O.ClientID);
-- Insert the orders into the results table --
SELECT *
FROM @Results;
GO;
一个称呼它:
exec pOrders @enteredClientID = 'ABC123456789'
或者简单地
exec pOrders 'ABC123456789'
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)