如何在SQL Server写一个foreach ?

我试图实现一些沿着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'。

817473 次浏览
小开

我想说，除了列idx实际上不存在于你所选择的表中之外，一切都可能正常工作。也许你想从@Practitioner中选择:

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
小开

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

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

(没有@)

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

小开

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

小开
最佳答案

你似乎想要使用CURSOR。虽然大多数时候最好使用基于集合的解决方案，但有时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
小开

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

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

  1. 创建一个临时表，把你想要迭代的记录放进去

  2. 使用WHILE @@ROWCOUNT <> 0进行迭代

  3. 每次获取一行do， SELECT TOP 1 <fieldnames>

    b.在变量中保存该行的唯一ID

  4. 执行操作，然后根据步骤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
小开

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

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
小开

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

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
小开

我编写了一个过程，对任何表使用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的语法和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

提交答案