SQL Server - Trouble passing variable to a stored procedure

Question

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

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

Here's the stored procedure:

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'

I get this error message back:

Must declare the scalar variable "@enteredClientID".

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

score 2 · Accepted Answer · answered Mar 12 '12 at 00:10

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 to call it:

exec pOrders @enteredClientID = 'ABC123456789'

or simply

exec pOrders 'ABC123456789'

score 1 · Answer 2 · answered Mar 11 '12 at 23:58

--In stored proc

DECLARE @enteredClientID varchar(20) OUTPUT

--Then insert and set identity

SELECT SCOPE_IDENTITY() AS @enteredClientID

--When calling procedure: -- DECLARE variables to hold the return value

DECLARE @enteredClientID VARCHAR(20);

-- Execute the procedure, which returns value.

EXEC @enteredClientID = pOrders

score 0 · Answer 3 · answered Mar 12 '12 at 00:04

0

Try EXEC pOrders 'ABC123456789'.

The call you provided attempts to execute the procedure (with no parameters passed), then attempts to set a variable named @enteredClientID. Since you have not declared @enteredClientID in the scope of the executing code, it cannot set it.

For more information about how to use parameters with procedures, this article may be helpful: http://msdn.microsoft.com/en-us/library/ms189915.aspx

answered Mar 12 '12 at 00:04

Rose

156
5

Just a suggestion: it may make sense to use a table-valued function instead of a procedure to meet this need...but that's outside of the scope of the question :-) – Rose Mar 12 '12 at 00:07

SQL Server - Trouble passing variable to a stored procedure

3 Answers3

Linked