SQL Server - 无法将变量传递给存储过程 [英] SQL Server - Trouble passing variable to a stored procedure

问题描述

我知道这是一个基本问题 - 但我想不出正确的方法来完成这项工作.

I know this is a basic question - but I can't figure the correct way to get this done.

我需要将一个变量传递给 SQL Server 2008 存储过程并返回查询.

I need to pass a variable to a SQL Server 2008 stored procedure and return the query.

这是存储过程:

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;

现在，我将执行存储过程并返回结果:

Now, I would execute the stored procedure and get the result back:

exec pOrders
set @enteredClientID = 'ABC123456789'

我收到此错误消息:

必须声明标量变量@enteredClientID".

Must declare the scalar variable "@enteredClientID".

但是，我声明的是变量......我错过了什么?

But, I'm declaring the variable.... what am I missing?

推荐答案

你没有声明一个参数，而是一个局部变量.按照您的意愿声明它:

You didn't declare a parameter, but a local variable. To declare it as you wanted to:

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;

An 调用它:

exec pOrders @enteredClientID = 'ABC123456789'

或者干脆

exec pOrders 'ABC123456789'

这篇关于SQL Server - 无法将变量传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！