Can I loop through a table variable in T-SQL?
SqlSql ServerTsqlDatabase CursorTable VariableSql Problem Overview
Is there anyway to loop through a table variable in T-SQL?
DECLARE @table1 TABLE ( col1 int )
INSERT into @table1 SELECT col1 FROM table2
I use cursors as well, but cursors seem less flexible than table variables.
DECLARE cursor1 CURSOR
FOR SELECT col1 FROM table2
OPEN cursor1
FETCH NEXT FROM cursor1
I would like to be able to use a table variable in the same manner as a cursor. That way I could execute some query on the table variable in one part of the procedure, and then later execute some code for each row in the table variable.
Any help is greatly appreciated.
Sql Solutions
Solution 1 - Sql
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
Solution 2 - 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.
Solution 3 - Sql
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
Solution 4 - Sql
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.
Solution 5 - Sql
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
Solution 6 - Sql
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.
Solution 7 - Sql
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
Solution 8 - Sql
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!
Solution 9 - Sql
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
Solution 10 - Sql
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
Solution 11 - Sql
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