0

I am new to write store procedures in sql I want to use a store procedure into another store procedure Test is a store procedure which works fine but in usage store procedure I want stock is a table and stocks is a variable having the result value of this table with three column i.e : siteId,productId,id so i need to send all these rows of this table to another store procedure

CREATE PROCEDURE USAGE AS BEGIN 
   DECLARE stocks table(siteId int, stockId int, productId int)
   DECLARE @i int
   set stocks  = 'insert into @stocks (siteId , stockId , productId)' + 
   '(select  siteId, id, productId from "Stock")'
SET @i = 1
 WHILE (@i<stocks.length)
   EXEC Test stocks[@i].siteId, stocks[@i].stockId, stocks[@i].productId   
  END
  select stocks
 END

unable to get stocks in array/table format for accessing it getting the error Msg 156, Level 15, State 1, Procedure USAGE, Line 2 Incorrect syntax near the keyword 'table'

  • 2
    Local variable names starts from @ char: DECLARE @stocks table(siteId int, stockId int, productId int) DECLARE (Transact-SQL), see Example C. – Akina Sep 25 at 10:26
2

If your goal is to execute the Test stored procedure for all stock rows, I'd recommend using a CURSOR to read each row from the stock table and pass the data into the Test stored procedure. Here is an example.

CREATE PROCEDURE USAGE
AS
BEGIN
    --Declare variables
    DECLARE @SiteId INT;
    DECLARE @StockId INT;
    DECLARE @ProductId INT;

    --Declare cursor to select rows from stock table
    DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT SiteId
        ,StockId
        ,ProductId
    FROM stock;

    --open the cursor
    OPEN _CURSOR;

    --fetch stock row into variables
    FETCH NEXT
    FROM _CURSOR
    INTO @SiteId
        ,@StockId
        ,@Productid;

    --for each stock row in the cursor, execute the Test stored procedure
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC Test @SiteId
            ,@StockId
            ,@ProductId;

        --fetch the next stock row in the cursor
        FETCH NEXT
        FROM _CURSOR
        INTO @SiteId
            ,@StockId
            ,@Productid;
    END --End While

    --close the cursor after processing all stock rows
    CLOSE _CURSOR

    --deallocate the cursor
    DEALLOCATE _CURSOR
END
  • Thanks....!!! It works for me – Kiwi Rupela Sep 25 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.