Posted By: Anonymous
I have the following SQL query:
DECLARE @MyVar datetime = '1/1/2010' SELECT @MyVar
This naturally returns ‘1/1/2010’.
What I want to do is have a list of dates, say:
1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010
Then i want to FOR EACH through the numbers and run the SQL Query.
Something like (pseudocode):
List = 1/1/2010,2/1/2010,3/1/2010,4/1/2010,5/1/2010 For each x in List do DECLARE @MyVar datetime = x SELECT @MyVar
So this would return:-
I want this to return the data as one resultset, not multiple resultsets, so I may need to use some kind of union at the end of the query, so each iteration of the loop unions onto the next.
I have a large query that accepts a ‘to date’ parameter, I need to run it 24 times, each time with a specific to date which I need to be able to supply (these dates are going to be dynamic) I want to avoid repeating my query 24 times with union alls joining them as if I need to come back and add additional columns it would be very time consuming.
SQL is primarily a set-orientated language – it’s generally a bad idea to use a loop in it.
In this case, a similar result could be achieved using a recursive CTE:
with cte as (select 1 i union all select i+1 i from cte where i < 5) select dateadd(d, i-1, '2010-01-01') from cte