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'.

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
)

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

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.

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

**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
```

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