SQL Server UNION multiple selects in a WHILE statement

Question

I have a WHILE statement in a stored procedure:

DECLARE @reverseindex int

DECLARE @index INT



SET @index = 1



WHILE (@index <= @workingyears)

BEGIN

    SET @reverseindex = @workingyears-@index+1



    SELECT daysallowed, date, holidaytype, leavetype, isactive 

    FROM setup_holiday_schedule 

    WHERE workingyears = @reverseindex

      AND holidayschedulecode = @holidayschedulecode

END

Running this query I get multiple tables depending on how big is

@workingyears

. I would like at the end of the WHILE statement to have just one table result.

Is that possible?

Answer 1

How about a table-valued variable?

DECLARE @reverseindex int
DECLARE @index INT
DECLARE @results TABLE (
    daysallowed INT,
    date DATE,
    holidaytype INT,
    leavetype INT,
    isactive BIT
)

SET @index = 1
WHILE (@index <= @workingyears)
BEGIN

    SET @reverseindex = @workingyears-@index+1
    INSERT INTO @results (
        daysallowed, date, holidaytype, leavetype, isactive
    )
    SELECT daysallowed, date, holidaytype, leavetype, isactive 
    FROM setup_holiday_schedule 
    WHERE workingyears = @reverseindex
    AND holidayschedulecode = @holidayschedulecode

END

SELECT daysallowed, date, holidaytype, leavetype, isactive FROM @results

SQL Server UNION multiple selects in a WHILE statement

Sign up