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

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

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

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

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

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

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

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

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

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

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

(没有@)

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

我做了一个过程，用CURSOR对任何表执行FOREACH。

使用示例:

CREATE TABLE #A (I INT, J INT)
INSERT INTO #A VALUES (1, 2), (2, 3)
EXEC PRC_FOREACH
    #A --Table we want to do the FOREACH
    , 'SELECT @I, @J' --The execute command, each column becomes a variable in the same type, so DON'T USE SPACES IN NAMES
   --The third variable is the database, it's optional because a table in TEMPB or the DB of the proc will be discovered in code

结果是每行2个选择。 UPDATE和break FOREACH的语法写在提示中。

这是过程代码:

CREATE PROC [dbo].[PRC_FOREACH] (@TBL VARCHAR(100) = NULL, @EXECUTE NVARCHAR(MAX)=NULL, @DB VARCHAR(100) = NULL) AS BEGIN

    --LOOP BETWEEN EACH TABLE LINE            

IF @TBL + @EXECUTE IS NULL BEGIN
    PRINT '@TBL: A TABLE TO MAKE OUT EACH LINE'
    PRINT '@EXECUTE: COMMAND TO BE PERFORMED ON EACH FOREACH TRANSACTION'
    PRINT '@DB: BANK WHERE THIS TABLE IS (IF NOT INFORMED IT WILL BE DB_NAME () OR TEMPDB)' + CHAR(13)
    PRINT 'ROW COLUMNS WILL VARIABLE WITH THE SAME NAME (COL_A = @COL_A)'
    PRINT 'THEREFORE THE COLUMNS CANT CONTAIN SPACES!' + CHAR(13)
    PRINT 'SYNTAX UPDATE:

UPDATE TABLE
SET COL = NEW_VALUE
WHERE CURRENT OF MY_CURSOR

CLOSE CURSOR (BEFORE ALL LINES):

IF 1 = 1 GOTO FIM_CURSOR'
    RETURN
END
SET @DB = ISNULL(@DB, CASE WHEN LEFT(@TBL, 1) = '#' THEN 'TEMPDB' ELSE DB_NAME() END)

    --Identifies the columns for the variables (DECLARE and INTO (Next cursor line))

DECLARE @Q NVARCHAR(MAX)
SET @Q = '
WITH X AS (
    SELECT
        A = '', @'' + NAME
        , B = '' '' + type_name(system_type_id)
        , C = CASE
            WHEN type_name(system_type_id) IN (''VARCHAR'', ''CHAR'', ''NCHAR'', ''NVARCHAR'') THEN ''('' + REPLACE(CONVERT(VARCHAR(10), max_length), ''-1'', ''MAX'') + '')''
            WHEN type_name(system_type_id) IN (''DECIMAL'', ''NUMERIC'') THEN ''('' + CONVERT(VARCHAR(10), precision) + '', '' + CONVERT(VARCHAR(10), scale) + '')''
            ELSE ''''
        END
    FROM [' + @DB + '].SYS.COLUMNS C WITH(NOLOCK)
    WHERE OBJECT_ID = OBJECT_ID(''[' + @DB + '].DBO.[' + @TBL + ']'')
    )
SELECT
    @DECLARE = STUFF((SELECT A + B + C FROM X FOR XML PATH('''')), 1, 1, '''')
    , @INTO = ''--Read the next line
FETCH NEXT FROM MY_CURSOR INTO '' + STUFF((SELECT A + '''' FROM X FOR XML PATH('''')), 1, 1, '''')'

DECLARE @DECLARE NVARCHAR(MAX), @INTO NVARCHAR(MAX)
EXEC SP_EXECUTESQL @Q, N'@DECLARE NVARCHAR(MAX) OUTPUT, @INTO NVARCHAR(MAX) OUTPUT', @DECLARE OUTPUT, @INTO OUTPUT

    --PREPARE TO QUERY

SELECT
    @Q = '
DECLARE ' + @DECLARE + '
-- Cursor to scroll through object names
DECLARE MY_CURSOR CURSOR FOR
    SELECT *
    FROM [' + @DB + '].DBO.[' + @TBL + ']

-- Opening Cursor for Reading
OPEN MY_CURSOR
' + @INTO + '

-- Traversing Cursor Lines (While There)
WHILE @@FETCH_STATUS = 0
BEGIN
    ' + @EXECUTE + '
    -- Reading the next line
    ' + @INTO + '
END
FIM_CURSOR:
-- Closing Cursor for Reading
CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR'

EXEC SP_EXECUTESQL @Q --MAGIA
END

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