我试图实现一些沿着for-each的线,我想采取返回的选择语句的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'。


当前回答

在你的版本中,下面这行是错误的:

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

(没有@)

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

其他回答

虽然游标通常被认为是可怕的邪恶,但我相信FAST_FORWARD游标是一个例子——在TSQL中你能得到的最接近FOREACH的东西。

在你的版本中,下面这行是错误的:

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

(没有@)

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

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

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

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

您似乎想要使用游标。尽管大多数时候最好使用基于集合的解决方案,但有时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