Can I loop through a table variable in T-SQL?

小开

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

Believe it or not, this is actually more efficient and performant than using a cursor.

小开

You can loop through the table variable or you can cursor through it. This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row.

I would suggest finding a SET-BASED answer to your question (we can help with that) and move away from rbars as much as possible.

小开

Here's another answer, similar to Justin's, but doesn't need an identity or aggregate, just a primary (unique) key.

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

小开

最佳答案

Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.

Try this:

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 @RowsToProcess=@@ROWCOUNT


SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectCol1=col1
FROM @table1
WHERE RowID=@CurrentRow


--do your thing here--


END

小开

Here's my variant. Pretty much just like all the others, but I only use one variable to manage the looping.

DECLARE
@LoopId  int
,@MyData  varchar(100)


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




INSERT @CheckThese (MyData)
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

Raj More's point is relevant--only perform loops if you have to.

小开

I didn't know about the WHILE structure.

The WHILE structure with a table variable, however, looks similar to using a CURSOR, in that you still have to SELECT the row into a variable based on the row IDENTITY, which is effectively a FETCH.

Is there any difference between using WHERE and something like the following?

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


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

I don't know if that's even possible. I suppose you might have to do this:

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

Thanks for you help!

小开

Here is my version of the same solution...

    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 fPat.PatientID=@id


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


end

小开

look like this demo:

DECLARE @Table TABLE (IdRow int not null identity(1,1), MyValue int);
insert into @Table select 345;
insert into @Table select 795;
insert into @Table select 565;


DECLARE @i int = 1;
DECLARE @rows int = (SELECT MAX(IdRow) FROM @Table);


WHILE @i <= @rows
BEGIN
DECLARE @MyValue int = (Select top 1 MyValue FROM @Table WHERE IdRow = @i);


print Concat('@i:', @i, ' ', '@MyValue:', @MyValue);


set @i = @i + 1;
END

Version without idRow, using ROW_NUMBER

DECLARE @Table TABLE (IdRow int not null identity(1,1), MyValue int);
insert into @Table select 345;
insert into @Table select 795;
insert into @Table select 565;


DECLARE @i int = 1;
DECLARE @max int = (select count(*) from @Table);


WHILE @i <= @max
BEGIN
DECLARE @MyValue int = (


select T1.MyValue
from (
select MyValue,
ROW_NUMBER() OVER(ORDER BY (select 1)) as RowId
from @Table
) T1
where T1.RowId = @i
);


print Concat('@i:', @i, ' ', '@MyValue:', @MyValue);


set @i = @i+1;
END

小开

My two cents.. From KM.'s answer, if you want to drop one variable, you can do a countdown on @RowsToProcess instead of counting up.

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 RowID=@RowsToProcess


--do your thing here--


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

小开

Following Stored Procedure loop through the Table Variable and Prints it in Ascending ORDER. This example is using WHILE LOOP.

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;

Following Statement Executes the Stored Procedure:

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

The result displayed in SQL Query window is shown below:

The function PARSE_COMMA_DELIMITED_INTEGER() that returns TABLE variable is as shown below :

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

小开

Select Top 1 can easily resolve it without the need of any sequence/order.

Create Function Test_Range()
Returns
@Result Table (ID Int)
As
Begin


Declare @ID Varchar(10) = ''
Declare @Rows Int, @Row Int = 0
Declare @Num Int, @RangeTo Int


Declare @RangeTable Table (ID Varchar(10), RangeFrom Int, RangeTo Int)
Insert Into @RangeTable Values ('A', 1, 10)
Insert Into @RangeTable Values ('B', 25,30)


Set @Rows = (Select Count(*) From @RangeTable)


While @Row <= @Rows
Begin
Set @Row = @Row + 1
Select Top 1 @ID = ID, @Num = RangeFrom, @RangeTo = RangeTo  From @RangeTable
Where ID > @ID
While @Num <= @RangeTo
Begin
Insert Into @Result Values (@Num)
Set @Num = @Num + 1
End
End
Return
End