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'.
Solution 1:
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
Solution 2:
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