如何在 SQL Server 中编写 foreach? [英] How to write a foreach in SQL Server?

问题描述

我正在尝试实现类似于 for-each 的东西，我想获取返回的 select 语句的 ID 并使用它们中的每一个.

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:

无效的列名idx".

推荐答案

您似乎想要使用 CURSOR.尽管大多数情况下最好使用基于集合的解决方案，但有时CURSOR 是最佳解决方案.在不了解您的实际问题的情况下，我们无法为您提供更多帮助:

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

这篇关于如何在 SQL Server 中编写 foreach?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！