Posted By: Anonymous
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’.
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