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 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 -  1 1 2010     2 1 2010     3 1 2010     4 1 2010     5 1 2010  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   edit  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

User · Answer

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  lt   EndYear            --Selecting the Year table  selec

User · Answer

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  lt   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 temp table   CREATE TABLE  MyVar Val DATETIME  DECLARE  I INT   StartDate DATETIME SET  I   1 SET  StartDate    20100101   WHILE  I  lt   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 is your main goal  as was said by  JohnFx  this could probably be done another  more efficient  way

User · Answer

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  lt  5  select dateadd d  i-1   2010-01-01   from cte

User · Answer

Off course an old question  But I have a simple solution where no need of Looping  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 Mircrosoft s undocumented table  It has numbers for every type  Its not suggestible to use as it can be removed in any new versions of sql server without prior information  since it is undocumented  But we can use it as quick workaround in some scenario s like above

User · Answer

This kind of depends on what you want to do with the results  If you re just after the numbers  a set-based option would be a numbers table - which comes in handy for all sorts of things   For MSSQL 2005   you can use a recursive CTE to generate a numbers table inline    WITH Numbers  N  AS       SELECT 1 UNION ALL     SELECT 1   N FROM Numbers WHERE N  lt  500    SELECT N FROM Numbers OPTION  MAXRECURSION 500

User · Answer

declare  counter as int set  counter   0 declare  date as varchar 50  set  date   cast 1  counter as varchar    01 2013  while  counter  lt  12  begin  select  cast 1  counter as varchar    01 2013  as date set  counter    counter   1 end

User · Answer

You could use a variable table  like this   declare  num int  set  num   1  declare  results table   val int    while   num  lt  6  begin   insert into  results   val   values    num     set  num    num   1 end  select val from  results

[sql] SQL Server FOR EACH Loop

Examples related to sql

Examples related to sql-server

Examples related to tsql