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

user_125292 0
Viewed 161741

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.

database-cursor table-variable tsql sql-server sql
11 Answers

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 Result of PrintSequenceSeries

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
user_69118240

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
user_25580310
Related