假设列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

这通常(几乎总是)比游标执行得更好，而且更简单:

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

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

创建一个临时表，并将想要迭代的记录放入其中 使用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

假设列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 (@i <= (SELECT MAX(idx) FROM @Practitioner))

(没有@)

也许可以改变您的命名约定，使表更加不同。

您的select count和select max应该来自您的表变量，而不是实际的表

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