How do i write foreach statement in sql

Question

In SQL Server I have written a stored procedure where I have declared an array like

declare @arr table (Position int, BTtype varchar(50))

and I have inserted values in to this array based on conditions my problem is I want to check like

for each(string type in string array(BT Type))

How do I check the same way in SQL?

For example I declare a local variable called type with datatype varchar and then how do I check like the for each loop written above in SQL........?? I would like to know how we can perform for each iteration in SQL ?

Check http://stackoverflow.com/questions/18513986/how-to-write-a-foreach-in-sql-server — MusicLovingIndianGirl, Dec 10 '13 at 06:28
If you post a real example of what you're trying to do, we can probably find you a solution that avoids loops altogether. Otherwise see below. — jdl, Dec 10 '13 at 06:31

score 0 · Accepted Answer · answered Dec 10 '13 at 06:26

0

DECLARE @Type varchar(50)
DECLARE c CURSOR FOR
SELECT
   BType
FROM @arr
OPEN c; FETCH NEXT FROM c INTO @Type
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Example
    UPDATE @arr SET [Position] = 0
    WHERE [BType] = @Type        

    FETCH NEXT FROM c INTO @Type
END
CLOSE c; DEALLOCATE c;

answered Dec 10 '13 at 06:26

jdl

1,104
8
12

3

Keep in mind cursors are horrible slow. It's much better using a `WHILE` loop. – SchmitzIT Dec 10 '13 at 06:27
@J LO:IS tat it? Will it select the values of BTType present in (@arr) if I just declare the cursor will it run through all values of bttype present in (@arr) ?? – user3085636 Dec 10 '13 at 06:31
@user3085636 Yeah. You can grab as many variables out of the table as you want. You can even call DISTINCT [BType] or filter on specific types if you want (for example). – jdl Dec 10 '13 at 06:32

score 0 · Answer 2 · answered Dec 10 '13 at 06:30

0

You can use while loop in sql for looping.Example of WHILE Loop

 DECLARE @intFlag INT
 SET @intFlag = 1
 WHILE (@intFlag <=5)
 BEGIN
     PRINT @intFlag
     SET @intFlag = @intFlag + 1
 END
 GO

For more example visit this link. While loop examples.

answered Dec 10 '13 at 06:30

Raghubar

2,768
1
21
31

How do i write foreach statement in sql

2 Answers2