SQL Server에서 foreach를 작성하는 방법은 무엇입니까?

SQL Server에서 foreach를 작성하는 방법은 무엇입니까?

---

나는 for-each 줄을 따라 무언가를 달성하려고 노력하고 있는데, 여기서 리턴 된 select 문의 ID를 가져 와서 각각을 사용하고 싶습니다.

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

현재 위와 같은 것이 있지만 오류가 발생합니다.

'idx'열 이름이 잘못되었습니다.

누군가가

---

을 사용하려는 것 같습니다 CURSOR. 대부분의 경우 세트 기반 솔루션을 사용하는 CURSOR것이 가장 좋지만 a 가 최고의 솔루션 인 경우가 있습니다. 실제 문제에 대해 더 많이 알지 못하면 그 이상을 도울 수 없습니다.

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

---

PractitionerId 열이 고유하다고 가정하면 다음 루프를 사용할 수 있습니다

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

---

선택 수와 선택 최대 값은 실제 테이블 대신 테이블 변수에서 가져와야합니다.

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

---

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
)

---

This generally (almost always) performs better than a cursor and is simpler:

    DECLARE @PractitionerList TABLE(PracticionerID INT)
    DECLARE @PractitionerID 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

---

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 need SQL Server Cursor.

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

---

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

---

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

참고URL : https://stackoverflow.com/questions/18513986/how-to-write-a-foreach-in-sql-server