T-SQL loop over query results

Question

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?

Answer 1

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.

Answer 2

You could do something like this:

create procedure test
as
BEGIN

    create table #ids
    (
        rn int,
        id int
    )
    
    insert into #ids (rn, id)
    select distinct row_number() over(order by id) as rn, id
    from table
    
    declare @id int
    declare @totalrows int = (select count(*) from #ids)
    declare @currentrow int = 0
    
    while @currentrow <  @totalrows  
    begin 
        set @id = (select id from #ids where rn = @currentrow + 1)
          
        exec stored_proc @varName=@id, @otherVarName='test'
        
        set @currentrow = @currentrow +1
    end  

END

Answer 3

My prefer solution is Microsoft KB 111401 http://support.microsoft.com/kb/111401.

The link refers to 3 examples:

This article describes various methods that you can use to simulate a cursor-like FETCH-NEXT logic in a stored procedure, trigger, or Transact-SQL batch.

/*********** example 1 ***********/ 

declare @au_id char( 11 )

set rowcount 0
select * into #mytemp from authors

set rowcount 1

select @au_id = au_id from #mytemp

while @@rowcount <> 0
begin
    set rowcount 0
    select * from #mytemp where au_id = @au_id
    delete #mytemp where au_id = @au_id

    set rowcount 1
    select @au_id = au_id from #mytemp
end
set rowcount 0



/********** example 2 **********/ 

declare @au_id char( 11 )

select @au_id = min( au_id ) from authors

while @au_id is not null
begin
    select * from authors where au_id = @au_id
    select @au_id = min( au_id ) from authors where au_id > @au_id
end



/********** example 3 **********/ 

set rowcount 0
select NULL mykey, * into #mytemp from authors

set rowcount 1
update #mytemp set mykey = 1

while @@rowcount > 0
begin
    set rowcount 0
    select * from #mytemp where mykey = 1
    delete #mytemp where mykey = 1
    set rowcount 1
    update #mytemp set mykey = 1
end
set rowcount 0

Answer 4

DECLARE @id INT
DECLARE @name NVARCHAR(100)
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT table.id,
       table.name
FROM   table

WHILE 1=1
BEGIN

    FETCH NEXT
    FROM @getid INTO @id, @name
    IF @@FETCH_STATUS < 0 BREAK

    EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name

END

CLOSE @getid
DEALLOCATE @getid

Answer 5

try this:

declare @i tinyint = 0,
    @count tinyint,
    @id int,
    @name varchar(max)

select @count = count(*) from table
while (@i < @count)
begin
    select @id = id, @name = name from table
    order by nr asc offset @i rows fetch next 1 rows only

    exec stored_proc @varName = @id, @otherVarName = 'test', @varForName = @name

    set @i = @i + 1
end

Answer 6

DECLARE @id INT
DECLARE @filename NVARCHAR(100)
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT top 3 id,
filename 
FROM  table

OPEN @getid
WHILE 1=1
BEGIN

    FETCH NEXT
    FROM @getid INTO @id, @filename
    IF @@FETCH_STATUS < 0 BREAK

    print @id

END


CLOSE @getid
DEALLOCATE @getid

Answer 7

I did not find more or less direct solution here, so I needed to use other resources and modify the code a little bit.

DECLARE @TrusteeElectionId INT;
    INSERT INTO dbo.TrusteeElections (CorpActId, ElectedOption )
    VALUES(@CorpActId,@ElectedOption)
    SET @trusteeElectionId = (select scope_identity())

    create table #ProductWrapperTypeIds
    (
        Id int
    )

     insert into #ProductWrapperTypeIds (id)
     SELECT value FROM STRING_SPLIT(@ProductWrapperTypes, ',') -- @ProductWrapperTypes is a story procedure parameter that looks like that '12,13,14' -- as a result of the code above #ProductWrapperTypeIds will have table with int IDs.


    declare @id int
    declare @totalrows int = (select count(*) from #ProductWrapperTypeIds)
    declare @currentId int = (SELECT MIN(Id) FROM  #ProductWrapperTypeIds) -- make sure we start from smaller id
    declare @currentrow int = (SELECT 0) -- counter starts from 0
    
    while @currentrow <  @totalrows  
   begin
    SET @id = NULL;
    SET @id = (select id from #ProductWrapperTypeIds where id = @currentId);
    IF @id IS NOT NULL
        BEGIN
              INSERT INTO dbo.TrusteeElectionProductTypes (ProductWrapperTypeId,TrusteeElectionId ) -- we don't want to insert NULL
              VALUES(@id, @TrusteeElectionId)
              SET @currentrow = @currentrow +1 -- we increase counter only if we find an id
        END
        SET @currentId = @currentId + 1 -- we promote id to the next item anyway


end

This example covers the conditions code too.