VAAA VAAA - 6 days ago 5
SQL Question

SQL Server UNION multiple selects in a WHILE statement

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?

3bh 3bh
Answer

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
Comments