loop in sql server code example

Example 1: sql for loop

/*
For loop in sql
*/
Example:
DECLARE 
   a number(2); 
BEGIN 
   FOR a in 10 .. 20 LOOP 
      dbms_output.put_line('value of a: ' || a); 
  END LOOP; 
END; 
/


Output:
value of a: 10 
value of a: 11 
value of a: 12 
value of a: 13 
value of a: 14 
value of a: 15 
value of a: 16 
value of a: 17 
value of a: 18 
value of a: 19 
value of a: 20

Example 2: sql server loop over query

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

Tags:

Sql Example