Menu
I run a query select @id=table.id from table and I need to loop over the results so I can exec a store procedure for each row exec stored_proc @varName=@id,@otherVarName='test'
How can I do this in a T-SQL script?
777
DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter. -- Use a table variable to hold numbered rows containg MyTable's ID values. DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL, ID INT )
SQL WHILE loop provides us with the advantage to execute the SQL statement(s) repeatedly until the specified condition result turn out to be false. In the following sections of this article, we will use more flowcharts in order to explain the notions and examples.
You could use a CURSOR in this case:
DECLARE @id INT DECLARE @name NVARCHAR(100) DECLARE @getid CURSOR SET @getid = CURSOR FOR SELECT table.id, table.name FROM table OPEN @getid FETCH NEXT FROM @getid INTO @id, @name WHILE @@FETCH_STATUS = 0 BEGIN EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name FETCH NEXT FROM @getid INTO @id, @name END CLOSE @getid DEALLOCATE @getid Modified to show multiple parameters from the table.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
