How to write a foreach in SQL Server

Question

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  gt  0     WHILE   i  lt    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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0             WHILE   i  lt    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

User · Answer

I came up with a very effective   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  lt  gt  0 to do the iterating     3  to get one row at a time do  SELECT TOP 1  lt fieldnames gt          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  lt  gt  0  --  lt  lt  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

User · Answer

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

User · Answer

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

User · Answer

The following line is wrong in your version   WHILE   i  lt    SELECT MAX idx  FROM  Practitioner      Missing the     Might be an idea to change your naming convention so that the tables are more different

User · Answer

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  gt   PractitionerId   IF  PractitionerId IS NULL BREAK   SELECT  PractitionerId END

User · Answer

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  lt    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

User · Answer

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  gt  0     WHILE   i  lt    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

[sql-server] How to write a foreach in SQL Server?

Examples related to sql-server

Examples related to tsql