Hoe schrijf je een foreach in SQL Server?

Ik probeer iets te bereiken in de trant van een for-each, waarbij ik de ID’s van een geretourneerde select-instructie zou willen nemen en elk van hen zou willen gebruiken.

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

Op dit moment heb ik iets dat lijkt op het bovenstaande, maar ik krijg de foutmelding:

Ongeldige kolomnaam ‘idx’.

Antwoord 1, autoriteit 100%

Het lijkt erop dat je een CURSORwilt gebruiken. Hoewel het meestal het beste is om een set-gebaseerde oplossing te gebruiken, zijn er soms momenten waarop een CURSORde beste oplossing is. Zonder meer te weten over uw echte probleem, kunnen we u niet verder helpen:

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

Antwoord 2, autoriteit 40%

Stel dat de kolom PractitionerId uniek is, dan kun je de volgende lus gebruiken

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

Antwoord 3, autoriteit 9%

Dit presteert over het algemeen (bijna altijd) beter dan een cursor en is eenvoudiger:

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

Antwoord 4, autoriteit 5%

Uw select count en select max moeten uit uw tabelvariabele komen in plaats van de werkelijke tabel

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

Antwoord 5

Ik zou zeggen dat alles waarschijnlijk werkt, behalve dat de kolom idxniet echt bestaat in de tabel waaruit u selecteert. Misschien wilde je een keuze maken uit @Practitioner:

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

omdat dat zo is gedefinieerd in de bovenstaande code:

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

Antwoord 6

De volgende regel is fout in uw versie:

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

(De @ ontbreekt)

Misschien een idee om je naamgevingsconventie te veranderen, zodat de tabellen meer van elkaar verschillen.

Antwoord 7

Ik heb een procedure gemaakt die een FOREACHmet CURSORuitvoert voor elke tabel.

Voorbeeld van gebruik:

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

Het resultaat is 2 selecties voor elke rij.
De syntaxis van UPDATEen het breken van de FOREACHstaan in de hints.

Dit is de procedurecode:

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

Antwoord 8

Hoewel Cursors meestal als vreselijk kwaad beschouwd, geloof ik dat dit een zaak is voor Fast_Forward Cursor – het dichtstbijzijnde dat je kunt krijgen in TSQL.

Antwoord 9

Hier is het een van de betere oplossingen.

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

Hier heb ik een aantal waarden in de tabel toevoegen, aanvankelijk is het leeg.

Wij hebben toegang of we kunnen alles in het lichaam van de lus doen en we hebben toegang tot de IDX door deze in de tabeldefinitie te definiëren.

             BEGIN
                SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i)
                --Do something with Id here
                PRINT @curren_val
                SET @i = @i + 1
            END

Antwoord 10

Ik heb een zeer effectieve, (denk ik) leesbare manier bedacht om dit te doen.

   1. create a temp table and put the records you want to iterate in there
    2. use WHILE @@ROWCOUNT <> 0 to do the iterating
    3. to get one row at a time do, SELECT TOP 1 <fieldnames>
        b. save the unique ID for that row in a variable
    4. Do Stuff, then delete the row from the temp table based on the ID saved at step 3b.

Hier is de code. Sorry, het gebruikt mijn variabelenamen in plaats van die in de vraag.

           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