Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    10

    Unanswered: Unable to receive data in varibales used in SQL Server stored procedures

    Hi,

    I trying to insert retreived values into varibles to use these values later in my code. I am using @var_sku as input parameter and using that i want to retrieve data from two other columns in the table.But SQL Server gives me an Error : 'Must declare the variable '@var_days' But i think i declared variables

    Following is the procedure i wrote:

    CREATE PROCEDURE dev_fetchlocation_stage1
    @var_sku varchar,
    @var_days numeric OUTPUT,
    @var_dateadded DATETIME OUTPUT
    AS

    SELECT @var_days = Min(A.Days), @var_dateadded = A.DateAdded FROM UniversalBooks.dbo.tblInventory AS A
    WHERE ((A.ISBN) = @var_sku
    AND ((A.QuantityOnHand)>0)
    AND ((A.DateAdded)=(SELECT Min(B.DateAdded)
    From UniversalBooks.dbo.tblInventory as B
    WHERE ((A.ISBN =B.ISBN) AND (B.QuantityOnHand>0)))))
    GROUP BY A.DateAdded

    GO

    **************************************************

    I am calling this procedure using following cursor:

    USE UniversalBooks
    Go
    Declare @var_orid varchar(25), @var_itemid varchar(25), @var_sku varchar(25)
    DECLARE Employee_Cursor CURSOR FOR
    SELECT OrderId, OrderItemId, Sku
    FROM UniversalBooks.dbo.tblProcessedOrdersImport

    OPEN Employee_Cursor
    FETCH NEXT FROM Employee_Cursor
    INTO @var_orid, @var_itemid, @var_sku
    WHILE @@FETCH_STATUS <> -1

    BEGIN

    EXEC dev_fetchlocation_stage1 @var_sku, @var_days OUTPUT, @var_dateadded OUTPUT
    FETCH NEXT FROM Employee_Cursor
    INTO @var_orid, @var_itemid, @var_sku
    END
    CLOSE Employee_Cursor
    DEALLOCATE Employee_Cursor

    Any help will be appericiated.
    Last edited by Devinder Gera; 09-26-03 at 01:51.
    Thanks in advance
    Devinder

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    you need to declare those variables in the code where you call that procedure from:

    Declare @var_orid varchar(25), @var_itemid varchar(25), @var_sku varchar(25), @var_days int, @var_dateadded datetime

    then you can call the proc:

    EXEC dev_fetchlocation_stage1 @var_sku, @var_days OUTPUT, @var_dateadded OUTPUT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •