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

Could someone

回答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

回答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

回答3:

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

回答4:

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
)

回答5:

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.

回答6:

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.

回答7:

You need SQL Server Cursor.

This is an MSDN link. Refer this for simple example.

回答8:

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