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
@workingyears
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