Run time fetch the value from a table and iterate these values and sent to another procedure

Question

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'

Answer 1

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