How to write a foreach in SQL Server?

I am trying to achieve something along the lines of a for-each, where I would like to take the Ids of a returned select statement and use each of them.

DECLARE @i int DECLARE @PractitionerId int DECLARE @numrows int DECLARE @Practitioner TABLE ( idx smallint Primary Key IDENTITY(1,1) , PractitionerId int ) INSERT @Practitioner SELECT distinct PractitionerId FROM Practitioner SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM Practitioner) IF @numrows > 0 WHILE (@i <= (SELECT MAX(idx) FROM Practitioner)) BEGIN SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i) --Do something with Id here PRINT @PractitionerId SET @i = @i + 1 END 

At the moment I have something that looks like the above, but am getting the error:

Invalid column name 'idx'.

10 Answers

You seem to want to use a CURSOR. Though most of the times it's best to use a set based solution, there are some times where a CURSOR is the best solution. Without knowing more about your real problem, we can't help you more than that:

DECLARE @PractitionerId int DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT PractitionerId FROM Practitioner OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @PractitionerId WHILE @@FETCH_STATUS = 0 BEGIN --Do something with Id here PRINT @PractitionerId FETCH NEXT FROM MY_CURSOR INTO @PractitionerId END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR 

Suppose that the column PractitionerId is a unique, then you can use the following loop

DECLARE @PractitionerId int = 0 WHILE(1 = 1) BEGIN SELECT @PractitionerId = MIN(PractitionerId) FROM dbo.Practitioner WHERE PractitionerId > @PractitionerId IF @PractitionerId IS NULL BREAK SELECT @PractitionerId END 

This generally (almost always) performs better than a cursor and is simpler:

DECLARE @PractitionerList TABLE(PracticionerID INT) DECLARE @PracticionerID INT INSERT @PractitionerList(PracticionerID) SELECT PracticionerID FROM Practitioner WHILE(1 = 1) BEGIN SET @PracticionerID = NULL SELECT TOP(1) @PracticionerID = PracticionerID FROM @PractitionerList IF @PracticionerID IS NULL BREAK PRINT 'DO STUFF' DELETE TOP(1) FROM @PractitionerList END 

Your select count and select max should be from your table variable instead of the actual table

DECLARE @i int DECLARE @PractitionerId int DECLARE @numrows int DECLARE @Practitioner TABLE ( idx smallint Primary Key IDENTITY(1,1) , PractitionerId int ) INSERT @Practitioner SELECT distinct PractitionerId FROM Practitioner SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM @Practitioner) IF @numrows > 0 WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner)) BEGIN SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i) --Do something with Id here PRINT @PractitionerId SET @i = @i + 1 END 

I would say everything probably works except that the column idx doesn't actually exist in the table you're selecting from. Maybe you meant to select from @Practitioner:

WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner)) 

because that's defined in the code above like that:

DECLARE @Practitioner TABLE ( idx smallint Primary Key IDENTITY(1,1) , PractitionerId int ) 

The following line is wrong in your version:

WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner)) 

(Missing the @)

Might be an idea to change your naming convention so that the tables are more different.

Although cursors usually considered horrible evil I believe this is a case for FAST_FORWARD cursor - the closest thing you can get to FOREACH in TSQL.

Here is the one of the better solutions.

DECLARE @i int DECLARE @curren_val int DECLARE @numrows int create table #Practitioner (idx int IDENTITY(1,1), PractitionerId int) INSERT INTO #Practitioner (PractitionerId) values (10),(20),(30) SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM #Practitioner) IF @numrows > 0 WHILE (@i <= (SELECT MAX(idx) FROM #Practitioner)) BEGIN SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i) --Do something with Id here PRINT @curren_val SET @i = @i + 1 END 

Here i've add some values in the table beacuse, initially it is empty.

We can access or we can do anything in the body of the loop and we can access the idx by defining it inside the table definition.

 BEGIN SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i) --Do something with Id here PRINT @curren_val SET @i = @i + 1 END 

I made a procedure that execute a FOREACH with CURSOR for any table.

Example of use:

CREATE TABLE #A (I INT, J INT) INSERT INTO #A VALUES (1, 2), (2, 3) EXEC PRC_FOREACH #A --Table we want to do the FOREACH , 'SELECT @I, @J' --The execute command, each column becomes a variable in the same type, so DON'T USE SPACES IN NAMES --The third variable is the database, it's optional because a table in TEMPB or the DB of the proc will be discovered in code 

The result is 2 selects for each row. The syntax of UPDATE and break the FOREACH are written in the hints.

This is the proc code:

CREATE PROC [dbo].[PRC_FOREACH] (@TBL VARCHAR(100) = NULL, @EXECUTE NVARCHAR(MAX)=NULL, @DB VARCHAR(100) = NULL) AS BEGIN --LOOP BETWEEN EACH TABLE LINE IF @TBL + @EXECUTE IS NULL BEGIN PRINT '@TBL: A TABLE TO MAKE OUT EACH LINE' PRINT '@EXECUTE: COMMAND TO BE PERFORMED ON EACH FOREACH TRANSACTION' PRINT '@DB: BANK WHERE THIS TABLE IS (IF NOT INFORMED IT WILL BE DB_NAME () OR TEMPDB)' + CHAR(13) PRINT 'ROW COLUMNS WILL VARIABLE WITH THE SAME NAME (COL_A = @COL_A)' PRINT 'THEREFORE THE COLUMNS CANT CONTAIN SPACES!' + CHAR(13) PRINT 'SYNTAX UPDATE: UPDATE TABLE SET COL = NEW_VALUE WHERE CURRENT OF MY_CURSOR CLOSE CURSOR (BEFORE ALL LINES): IF 1 = 1 GOTO FIM_CURSOR' RETURN END SET @DB = ISNULL(@DB, CASE WHEN LEFT(@TBL, 1) = '#' THEN 'TEMPDB' ELSE DB_NAME() END) --Identifies the columns for the variables (DECLARE and INTO (Next cursor line)) DECLARE @Q NVARCHAR(MAX) SET @Q = ' WITH X AS ( SELECT A = '', @'' + NAME , B = '' '' + type_name(system_type_id) , C = CASE WHEN type_name(system_type_id) IN (''VARCHAR'', ''CHAR'', ''NCHAR'', ''NVARCHAR'') THEN ''('' + REPLACE(CONVERT(VARCHAR(10), max_length), ''-1'', ''MAX'') + '')'' WHEN type_name(system_type_id) IN (''DECIMAL'', ''NUMERIC'') THEN ''('' + CONVERT(VARCHAR(10), precision) + '', '' + CONVERT(VARCHAR(10), scale) + '')'' ELSE '''' END FROM [' + @DB + '].SYS.COLUMNS C WITH(NOLOCK) WHERE OBJECT_ID = OBJECT_ID(''[' + @DB + '].DBO.[' + @TBL + ']'') ) SELECT @DECLARE = STUFF((SELECT A + B + C FROM X FOR XML PATH('''')), 1, 1, '''') , @INTO = ''--Read the next line FETCH NEXT FROM MY_CURSOR INTO '' + STUFF((SELECT A + '''' FROM X FOR XML PATH('''')), 1, 1, '''')' DECLARE @DECLARE NVARCHAR(MAX), @INTO NVARCHAR(MAX) EXEC SP_EXECUTESQL @Q, N'@DECLARE NVARCHAR(MAX) OUTPUT, @INTO NVARCHAR(MAX) OUTPUT', @DECLARE OUTPUT, @INTO OUTPUT --PREPARE TO QUERY SELECT @Q = ' DECLARE ' + @DECLARE + ' -- Cursor to scroll through object names DECLARE MY_CURSOR CURSOR FOR SELECT * FROM [' + @DB + '].DBO.[' + @TBL + '] -- Opening Cursor for Reading OPEN MY_CURSOR ' + @INTO + ' -- Traversing Cursor Lines (While There) WHILE @@FETCH_STATUS = 0 BEGIN ' + @EXECUTE + ' -- Reading the next line ' + @INTO + ' END FIM_CURSOR: -- Closing Cursor for Reading CLOSE MY_CURSOR DEALLOCATE MY_CURSOR' EXEC SP_EXECUTESQL @Q --MAGIA END 

I came up with a very effective and (I think) readable way to do this.

1. Create a temp table and put the records you want to iterate in there

2. Use WHILE @@ROWCOUNT <> 0 to do the iterating

3. To get one row at a time do, SELECT TOP 1 <fieldnames>

   b. save the unique ID for that row in a variable

4. Do stuff, then delete the row from the temp table based on the ID saved at step 3b.

Here's the code. Sorry, its using my variable names instead of the ones in the question.

DECLARE @tempPFRunStops TABLE ( ProformaRunStopsID int, ProformaRunMasterID int, CompanyLocationID int, StopSequence int ); INSERT @tempPFRunStops (ProformaRunStopsID, ProformaRunMasterID, CompanyLocationID, StopSequence) SELECT ProformaRunStopsID, ProformaRunMasterID, CompanyLocationID, StopSequence FROM ProformaRunStops WHERE ProformaRunMasterID IN ( SELECT ProformaRunMasterID FROM ProformaRunMaster WHERE ProformaId = 15 ) -- SELECT * FROM @tempPFRunStops WHILE @@ROWCOUNT <> 0 -- << I dont know how this works BEGIN SELECT TOP 1 * FROM @tempPFRunStops -- I could have put the unique ID into a variable here SELECT 'Ha' -- Do Stuff DELETE @tempPFRunStops WHERE ProformaRunStopsID = (SELECT TOP 1 ProformaRunStopsID FROM @tempPFRunStops) END