CODEWITHSUNDEEP
sqlsql-serverloopswhile-loop
Sinnerv
Sinnerv

Reputation: 263

Loop for stored procedure

I have a Stored procedure that writes data to a table.

What i want to do is use a Variable in the stored procedure
and that variable to be assigned item numbers taken from a separate query

For example : select ITEM_NUMBER from xTable returns

 ITEM_NUMBER
------------
    A1
    B2
    C4
    D7
    J9

I want a loop to assign The variable each of these from A1 to J9 and run the stored procedure each time

this will run the stored procedure multiple times to run for all the items in my results table.

Thanks

Upvotes: 0

Views: 1130

Answers (4)

philip.yuile
philip.yuile

Reputation: 476

It sounds like your best option here is to use a cursor.

The cursor loops through the query and sets a variable from each row of the query's results. You can call the stored procedure in the body of the cursor, passing in the variable you have populated as a parameter to the procedure.

The syntax for a cursor is as follows:

DECLARE @item CHAR(2)

DECLARE item_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT ITEM_NUMBER from xTable

OPEN item_cursor

FETCH NEXT FROM item_cursor INTO @item

WHILE @@FETCH_STATUS = 0
BEGIN

--Execute your stored procedure here, supplying @item as the parameter

FETCH NEXT FROM item_cursor INTO @item

END

CLOSE item_cursor

DEALLOCATE item_cursor

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 3729

Try this using CURSOR.

DECLARE @sItemNumber AS VARCHAR(MAX)

DECLARE TestCursor CURSOR  FOR
(
    select ITEM_NUMBER from xTable
)

OPEN TestCursor
FETCH NEXT FROM TestCursor INTO @sItemNumber

WHILE @@FETCH_STATUS = 0
BEGIN
     EXEC ProcedureName @sItemNumber
     FETCH NEXT FROM TestCursor INTO @sItemNumber
END

CLOSE TestCursor
DEALLOCATE TestCursor

Upvotes: 0

Jaques
Jaques

Reputation: 2277

Two ways

Select ITEM_NUMBER into #X from xTable

DECLARE @PartVar varchar(10)
WHILE(0 < (Select Count(*) from #X))
  BEGIN
  SET ROWCOUNT 1
  Select @PartVar = ITEM_NUMBER from #X
  -- EXEC PROC @PartVar
  SET ROWCOUNT 0
  DELETE #X where ITEM_NUMBER = @PartVar
  END

or

DECLARE @PartVar varchar(10)
DECLARE LOC CURSOR FORWARD_ONLY FOR SELECT ITEM_NUMBER from xTable
OPEN LOC
FETCH NEXT FROM LOC into @PartVar

WHILE(@@FETCH_STATUS = 0)
BEGIN
  EXEC PROC @PartVar
  FETCH NEXT FROM LOC into @PartVar
END

CLOSE LOC
DEALLOCATE LOC

http://msdn.microsoft.com/en-us/library/ms180169.aspx

FORWARD_ONLY Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors.

Upvotes: 1

hatem87
hatem87

Reputation: 370

Try like this using 

DECLARE @itemNumber VARCHAR(50);

DECLARE db_cursor CURSOR FOR
SELECT ITEM_NUMBER FROM xTable;

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @itemNumber


WHILE @@FETCH_STATUS = 0  
BEGIN  
       
	   -- call your stored procedure here

       FETCH NEXT FROM db_cursor INTO @itemNumber  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Upvotes: 0

Related Questions