CODEWITHSUNDEEP
sql-servert-sql
Missy
Missy

Reputation: 1368

How to use a While Loop in SQL to Create a Variable

In need to create an IN clause with a list of dates in it. The list needs to be in descending order. I created a variable named @cols and am trying to populate it with this code:

declare @end date='2016/05/30' 
declare @begin date = DATEADD(month, DATEDIFF(month, 0, @end), 0) ;
declare @curdate date = @end;  -- start on the last day
print @curdate;
print @begin;

DECLARE @cols NVARCHAR (MAX)

while @curdate >=@begin  -- goes from end of the month to beginning of the month
begin
     select @cols = @cols + ',[' + CONVERT(NVARCHAR, @curdate, 106) + ']';
     select @curdate =  DATEADD(DAY,-1,@curdate ) -- subtract a day
end

print @cols;   
print @curdate;
print @begin;

I'm hoping to get 5/30/16, 5/29/16, 5/28/16 etc (properly formatted of course). The code runs without error however @cols is always empty.

Upvotes: 1

Views: 65

Answers (2)

Anthony Hancock
Anthony Hancock

Reputation: 931

Why are you passing a list of dates to an IN clause? Can you not just use a simple date range such as below?

DECLARE @enddate = '2016-05-30'

SELECT *
FROM sometable
WHERE somedate >= DATEADD(month, DATEDIFF(month, 0, @enddate), 0)
    AND somedate < EOMONTH(@enddate)

Upvotes: 0

Siyual
Siyual

Reputation: 16917

The reason you're getting a NULL @Cols is because it hasn't been assigned a value before entering the while loop. It is still NULL, and NULL + anything = NULL.

To remedy this, you can set @Cols equal to an empty string before the while loop:

DECLARE @cols NVARCHAR (MAX) = ''

Upvotes: 5

Related Questions