我想说，除了列idx在你选择的表中不存在之外，一切都可能正常。也许你想从@从业者中选择:

WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))

因为在上面的代码中是这样定义的:

DECLARE @Practitioner TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , PractitionerId int
)

您似乎想要使用游标。尽管大多数时候最好使用基于集合的解决方案，但有时CURSOR是最佳解决方案。在不了解你的实际问题的情况下，我们帮不了你:

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

我想说，除了列idx在你选择的表中不存在之外，一切都可能正常。也许你想从@从业者中选择:

WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))

因为在上面的代码中是这样定义的:

DECLARE @Practitioner TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , PractitionerId int
)

这是一个更好的解决方案。

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

这里我在表中添加了一些值，因为最初它是空的。

我们可以访问或在循环体中做任何事情，我们可以通过在表定义中定义idx来访问它。

              BEGIN
                SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i)

                --Do something with Id here

                PRINT @curren_val
                SET @i = @i + 1
            END

假设列practictionerid是唯一的，那么您可以使用下面的循环

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

我想出了一个非常有效且(我认为)可读的方法来做到这一点。

创建一个临时表，并将想要迭代的记录放入其中 使用WHILE @@ROWCOUNT <> 0进行迭代 SELECT TOP 1 <fieldnames> b.在变量中保存该行的唯一ID 执行操作，然后根据步骤3b保存的ID从临时表中删除该行。

这是代码。对不起，它使用了我的变量名，而不是问题中的变量名。

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