SQL Server FOR EACH Loop

Question

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 a list of dates, let's say:

 1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010 

Then I want to EVERYONE through numbers and run an SQL query.

Something like (pseudo code):

 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 will return: -

1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010

I want this to return data as one result set, not multiple result sets, so I might need to use some kind of union at the end of the query, so every loop loop iteration to the next.

change

I have a big request that accepts the "up to date" parameter, I need to run it 24 times, each time with specific dates that I need to provide (these dates will be dynamic) I want to avoid repeating my request 24 times with the union of all joining to them, as if I need to go back and add additional columns, it will be very time consuming.

Answer 1

SQL is, above all, a set-oriented language — it is usually a bad idea to use a loop in it.

In this case, a similar result can be achieved using 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 

Answer 2

Here is an option with a table variable:

 DECLARE @MyVar TABLE(Val DATETIME) DECLARE @I INT, @StartDate DATETIME SET @I = 1 SET @StartDate = '20100101' WHILE @I <= 5 BEGIN INSERT INTO @MyVar(Val) VALUES(@StartDate) SET @StartDate = DATEADD(DAY,1,@StartDate) SET @I = @I + 1 END SELECT * FROM @MyVar 

You can do the same with a temporary table:

 CREATE TABLE #MyVar(Val DATETIME) DECLARE @I INT, @StartDate DATETIME SET @I = 1 SET @StartDate = '20100101' WHILE @I <= 5 BEGIN INSERT INTO #MyVar(Val) VALUES(@StartDate) SET @StartDate = DATEADD(DAY,1,@StartDate) SET @I = @I + 1 END SELECT * FROM #MyVar 

You should tell us what your main goal is, as @JohnFx said, this can probably be done in a different (more efficient) way.

Answer 3

You can use a variable table, for example:

 declare @num int set @num = 1 declare @results table ( val int ) while (@num < 6) begin insert into @results ( val ) values ( @num ) set @num = @num + 1 end select val from @results 

Answer 4

This view depends on what you want to do with the results. If you are immediately after the numbers, the parameter based on the set will be a table of numbers - this is useful for all kinds of things.

For MSSQL 2005+, you can use the recursive CTE to create a line of inline numbers:

 ;WITH Numbers (N) AS ( SELECT 1 UNION ALL SELECT 1 + N FROM Numbers WHERE N < 500 ) SELECT N FROM Numbers OPTION (MAXRECURSION 500) 

Answer 5

 declare @counter as int set @counter = 0 declare @date as varchar(50) set @date = cast(1+@counter as varchar)+'/01/2013' while(@counter < 12) begin select cast(1+@counter as varchar)+'/01/2013' as date set @counter = @counter + 1 end 

Answer 6

Of course, the old question. But I have a simple solution where there is no need for a loop, CTE, table variables, etc.

 DECLARE @MyVar datetime = '1/1/2010' SELECT @MyVar SELECT DATEADD (DD,NUMBER,@MyVar) FROM master.dbo.spt_values WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 4 ORDER BY NUMBER 

Note: spt_values is a spt_values -documented table. He has numbers for each type. It cannot be used, since it can be removed in any new versions of SQL Server without prior information, since it has no documents. But we can use it as a quick workaround in some scenario, as described above.

Answer 7

 [CREATE PROCEDURE [rat].[GetYear] AS BEGIN -- variable for storing start date Declare @StartYear as int -- Variable for the End date Declare @EndYear as int -- Setting the value in strat Date select @StartYear = Value from rat.Configuration where Name = 'REPORT_START_YEAR'; -- Setting the End date select @EndYear = Value from rat.Configuration where Name = 'REPORT_END_YEAR'; -- Creating Tem table with [Years] as ( --Selecting the Year select @StartYear [Year] --doing Union union all -- doing the loop in Years table select Year+1 Year from [Years] where Year < @EndYear ) --Selecting the Year table selec]