wake-up-neo.com

Kann ich eine Tabellenvariable in T-SQL durchlaufen?

Gibt es überhaupt eine Tabellenvariable in T-SQL durchlaufen?

DECLARE @table1 TABLE ( col1 int )  
INSERT into @table1 SELECT col1 FROM table2

Ich verwende auch Cursor, aber Cursor scheinen weniger flexibel zu sein als Tabellenvariablen.

DECLARE cursor1 CURSOR  
    FOR SELECT col1 FROM table2  
OPEN cursor1  
FETCH NEXT FROM cursor1

Ich möchte eine Tabellenvariable wie einen Cursor verwenden. Auf diese Weise konnte ich einige Abfragen für die Tabellenvariable in einem Teil der Prozedur ausführen und später Code für jede Zeile in der Tabellenvariablen ausführen.

Jede Hilfe wird sehr geschätzt.

63
Kuyenda

Fügen Sie Ihrer Tabellenvariablen eine Identität hinzu und führen Sie eine einfache Schleife von 1 bis @@ ROWCOUNT des INSERT-SELECT aus.

Versuche dies:

DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     int

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @[email protected]@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @[email protected]+1
    SELECT 
        @SelectCol1=col1
        FROM @table1
        WHERE [email protected]

    --do your thing here--

END
91
KM.
DECLARE @table1 TABLE (
    idx int identity(1,1),
    col1 int )

DECLARE @counter int

SET @counter = 1

WHILE(@counter < SELECT MAX(idx) FROM @table1)
BEGIN
    DECLARE @colVar INT

    SELECT @colVar = col1 FROM @table1 WHERE idx = @counter

    -- Do your work here

    SET @counter = @counter + 1
END

Ob Sie es glauben oder nicht, das ist tatsächlich effizienter und leistungsfähiger als die Verwendung eines Cursors.

14
Justin Niessner

Sie können die Tabellenvariable durchlaufen oder den Cursor bewegen. Dies ist, was wir normalerweise RBAR nennen - ausgesprochen Reebar und bedeutet Row-By-Agonizing-Row.

Ich würde vorschlagen, eine SET-basierte Antwort auf Ihre Frage zu finden (wir können dabei helfen) und sich so weit wie möglich von Rbars entfernen.

6
Raj More

Meine zwei Cents ... Wenn Sie eine Variable löschen möchten, können Sie aus der Antwort von KM. Einen Countdown auf @RowsToProcess durchführen, anstatt aufwärts zu zählen.

DECLARE @RowsToProcess  int;

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @RowsToProcess = @@ROWCOUNT 

WHILE @RowsToProcess > 0 -- Countdown
BEGIN
    SELECT *
        FROM @table1
        WHERE [email protected]

    --do your thing here--

    SET @RowsToProcess = @RowsToProcess - 1; -- Countdown
END
6
typoerrpr

sieh wie diese Demo aus:

DECLARE @vTable TABLE (IdRow int not null primary key identity(1,1),ValueRow int);

-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------

DECLARE @cnt int = 1;
DECLARE @max int = (SELECT MAX(IdRow) FROM @vTable);

WHILE @cnt <= @max
BEGIN
    DECLARE @tempValueRow int = (Select ValueRow FROM @vTable WHERE IdRow = @cnt);

    ---work demo----
    print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
    print '@cnt:' + convert(varchar(10),@cnt);
    print'';
    --------------

    set @cnt = @cnt+1;
END

Version ohne idRow mit ROW_NUMBER

    DECLARE @vTable TABLE (ValueRow int);
-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------

DECLARE @cnt int = 1;
DECLARE @max int = (select count(*) from @vTable);

WHILE @cnt <= @max
BEGIN
    DECLARE @tempValueRow int = (
        select ValueRow 
        from (select ValueRow
            , ROW_NUMBER() OVER(ORDER BY (select 1)) as RowId 
            from @vTable
        ) T1 
    where t1.RowId = @cnt
    );

    ---work demo----
    print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
    print '@cnt:' + convert(varchar(10),@cnt);
    print'';
    --------------

    set @cnt = @cnt+1;
END
5
Gildor

Hier ist eine weitere Antwort, ähnlich wie Justin, jedoch ohne Identität oder Aggregat, nur einen primären (eindeutigen) Schlüssel.

declare @table1 table(dataKey int, dataCol1 varchar(20), dataCol2 datetime)
declare @dataKey int
while exists select 'x' from @table1
begin
    select top 1 @dataKey = dataKey 
    from @table1 
    order by /*whatever you want:*/ dataCol2 desc

    -- do processing

    delete from @table1 where dataKey = @dataKey
end
2

Ich wusste nichts über die WHILE-Struktur.

Die WHILE-Struktur mit einer Tabellenvariablen sieht jedoch der Verwendung eines CURSOR ähnlich aus, da Sie immer noch die Zeile in eine Variable auf der Grundlage der Zeile IDENTITY, die eigentlich ein FETCH ist, auswählen muss.

Gibt es einen Unterschied zwischen WO und etwas wie dem Folgenden?

DECLARE @table1 TABLE ( col1 int )  
INSERT into @table1 SELECT col1 FROM table2

DECLARE cursor1 CURSOR  
    FOR @table1
OPEN cursor1  
FETCH NEXT FROM cursor1

Ich weiß nicht, ob das überhaupt möglich ist. Ich nehme an, Sie müssen das vielleicht tun:

DECLARE cursor1 CURSOR  
    FOR SELECT col1 FROM @table1
OPEN cursor1  
FETCH NEXT FROM cursor1

Danke für deine Hilfe!

2
Kuyenda

Hier ist meine Variante. So wie alle anderen auch, aber ich verwende nur eine Variable, um das Looping zu verwalten.

DECLARE
  @LoopId  int
 ,@MyData  varchar(100)

DECLARE @CheckThese TABLE
 (
   LoopId  int  not null  identity(1,1)
  ,MyData  varchar(100)  not null
 )


INSERT @CheckThese (YourData)
 select MyData from MyTable
 order by DoesItMatter

SET @LoopId = @@rowcount

WHILE @LoopId > 0
 BEGIN
    SELECT @MyData = MyData
     from @CheckThese
     where LoopId = @LoopId

    --  Do whatever

    SET @LoopId = @LoopId - 1
 END

Der Punkt von Raj More ist relevant - führen Sie nur Schleifen aus, wenn Sie müssen.

2
Philip Kelley

Hier ist meine Version der gleichen Lösung ...

    declare @id int

        SELECT @id = min(fPat.PatientID)
        FROM tbPatients fPat
        WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)

while @id is not null
begin
    SELECT fPat.PatientID, fPat.InsNotes
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0) AND [email protected]

    SELECT @id = min(fPat.PatientID)
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)AND fPat.PatientID>@id

end
1
Gary Kindel

Folgen Sie der gespeicherten Prozedur durch die Tabellenvariable und drucken Sie sie in aufsteigender Reihenfolge. In diesem Beispiel wird WHILE LOOP verwendet. 

CREATE PROCEDURE PrintSequenceSeries 
    -- Add the parameters for the stored procedure here
    @ComaSeperatedSequenceSeries nVarchar(MAX)  
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @SERIES_COUNT AS INTEGER
    SELECT @SERIES_COUNT = COUNT(*) FROM PARSE_COMMA_DELIMITED_INTEGER(@ComaSeperatedSequenceSeries, ',')  --- ORDER BY ITEM DESC

    DECLARE @CURR_COUNT AS INTEGER
    SET @CURR_COUNT = 1

    DECLARE @SQL AS NVARCHAR(MAX)

    WHILE @CURR_COUNT <= @SERIES_COUNT
    BEGIN
        SET @SQL = 'SELECT TOP 1 T.* FROM ' + 
            '(SELECT TOP ' + CONVERT(VARCHAR(20), @CURR_COUNT) + ' * FROM PARSE_COMMA_DELIMITED_INTEGER( ''' + @ComaSeperatedSequenceSeries + ''' , '','') ORDER BY ITEM ASC) AS T ' +
            'ORDER BY T.ITEM DESC '
        PRINT @SQL 
        EXEC SP_EXECUTESQL @SQL 
        SET @CURR_COUNT = @CURR_COUNT + 1
    END;

Die folgende Anweisung führt die gespeicherte Prozedur aus:

EXEC  PrintSequenceSeries '11,2,33,14,5,60,17,98,9,10'

Das im SQL-Abfragefenster angezeigte Ergebnis ist unten dargestellt:

 The Result of PrintSequenceSeries

Die Funktion PARSE_COMMA_DELIMITED_INTEGER (), die die TABLE-Variable zurückgibt, sieht wie folgt aus:

CREATE FUNCTION [dbo].[parse_comma_delimited_integer]
        (
            @LIST       VARCHAR(8000), 
            @DELIMITER  VARCHAR(10) = ',
            '
        )

        -- TABLE VARIABLE THAT WILL CONTAIN VALUES
        RETURNS @TABLEVALUES TABLE 
        (
            ITEM INT
        )
        AS
        BEGIN 
            DECLARE @ITEM VARCHAR(255)

            /* LOOP OVER THE COMMADELIMITED LIST */
            WHILE (DATALENGTH(@LIST) > 0)
                BEGIN 
                    IF CHARINDEX(@DELIMITER,@LIST) > 0
                        BEGIN
                            SELECT @ITEM = SUBSTRING(@LIST,1,(CHARINDEX(@DELIMITER, @LIST)-1))
                            SELECT @LIST =  SUBSTRING(@LIST,(CHARINDEX(@DELIMITER, @LIST) +
                            DATALENGTH(@DELIMITER)),DATALENGTH(@LIST))
                        END
                    ELSE
                        BEGIN
                            SELECT @ITEM = @LIST
                            SELECT @LIST = NULL
                        END

                    -- INSERT EACH ITEM INTO TEMP TABLE
                    INSERT @TABLEVALUES 
                    (
                        ITEM
                    )
                    SELECT ITEM = CONVERT(INT, @ITEM) 
                END
        RETURN
        END
0
Rahul Varadkar