locked
using for loop in sql server SP RRS feed

  • Question

  •  

    Is there a way for using for loop inside an SP in SQL server 2005?

     

    If so, what is the syntax??

    Tuesday, May 13, 2008 1:28 PM

Answers

  • You can loop through a result set in sql using CURSORs or using a WHILE loop. Books Online has plenty of examples of syntax on both.

     

    HTH!

    • Marked as answer by Kalman Toth Saturday, August 7, 2010 5:31 AM
    Tuesday, May 13, 2008 1:33 PM
  • You could possibly use WHILE and variables as in the example below, but as Rich and Anthony already posted a set based solution outperforms any type of looping or cursor based approach.

     

     

    Code Snippet

    DECLARE ARE @dt DATETIME

    SET @dt = '05/10/2008'

    DECLARE @mon INT

    DECLARE @sun INT

    SET @mon = 0

    SET @sun = 0

     

    WHILE @dt <= '05/31/2008'

    BEGIN

     

    IF (SELECT DATEPART(DW, @dt)) = 1

    BEGIN

    SET @sun = @sun +1

    END

     

    IF (SELECT DATEPART(DW, @dt)) = 2

    BEGIN

    SET @mon = @mon +1

    END

     

    SET @dt = DATEADD(DAY, +1, @dt)

    END

     

    SELECT @sun AS Sunday,

           @mon AS Monday

     

     

     

    • Marked as answer by Kalman Toth Saturday, August 7, 2010 5:32 AM
    Tuesday, May 13, 2008 3:46 PM
  • see what i have done for this situation

    DECLARE @FROMDATE DATETIME
    DECLARE @TODATE DATETIME
    declare @histdate datetime


    SET @FROMDATE='2010-07-05 00:00:00'
    SET @TODATE= '2010-07-05 00:00:00'
    set @histdate = @FROMDATE
    TEST:
     IF @HISTDATE<=@TODATE
      BEGIN 

       SET @HISTDATE=DATEADD(DAY,1,@HISTDATE) -- ADDING ONE DATE
       GOTO TEST -- IF DAYS REMAINING THEN CALCULATE FOR ONE MORE DAY
      END

    • Marked as answer by Kalman Toth Saturday, August 7, 2010 5:34 AM
    Wednesday, July 21, 2010 11:04 AM
  • Unlike in algorithmic programming languages, you need to "loop" very rarely in T-SQL because looping on unordered sets is built into the SQL language. It is called set-based operations. In the following demo, the database engine loops through the entire Product table with 504 rows without explicit looping command:

    -- SET BASED OPERATION example
    SELECT	FinishedGood=CASE	WHEN FinishedGoodsFlag=1 
    							THEN'Yes'ELSE'No'END, 
    		ItemCount=COUNT(*)
    FROM AdventureWorks2012.Production.Product
    GROUP BY FinishedGoodsFlag
    ORDER BY FinishedGoodsFlag DESC;
    
    /* FinishedGood	ItemCount
    	Yes			295
    	No			209
    */

    Certain tasks require looping, for example, index REBUILD for all tables in a database:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by Naomi N Sunday, August 8, 2010 4:13 AM
    • Marked as answer by Kalman Toth Thursday, September 27, 2012 7:28 PM
    • Edited by Kalman Toth Thursday, September 27, 2012 7:33 PM
    Saturday, August 7, 2010 5:43 AM

All replies

  • You can loop through a result set in sql using CURSORs or using a WHILE loop. Books Online has plenty of examples of syntax on both.

     

    HTH!

    • Marked as answer by Kalman Toth Saturday, August 7, 2010 5:31 AM
    Tuesday, May 13, 2008 1:33 PM
  •  

    Thanks richbrownesq  for your response.

    SO u mean to say that FOR loop is not supported....

     

    Ok

     

    Tuesday, May 13, 2008 3:00 PM
  • Yes, there is no FOR loop in TSQL. 

     

    However, most likely you could perform the operation using TSQL the way it was meant to be used, set operations and not row by row logic.  If this cannot be done then you can use WHILE loops or CURSORS.

     

    If you give us an example of your situation we could provide an appropriate solution.

    Tuesday, May 13, 2008 3:07 PM
    Answerer
  • Good point Anthony. T-SQL is highly efficient at running set based operations and you may be able to rewrite your queries as such.

     

    A futher alternative could be to leverage the CLR capabilities of SQL2005 and write a CLR stored procedure which may (or may not) provide an efficient solution. Again, Books Online has some good material.


    HTH!

    Tuesday, May 13, 2008 3:17 PM
  • My situation is  like this:

     

    Inside a cursor , I get the count of fields in a table based on a condition.
    Now i need to loop that many times and calculate a yes/no condition and get the counts of YES and NOs.

     

    This prompted me to go for a for loop.

     

     

     

    Tuesday, May 13, 2008 3:27 PM
  • You could possibly use WHILE and variables as in the example below, but as Rich and Anthony already posted a set based solution outperforms any type of looping or cursor based approach.

     

     

    Code Snippet

    DECLARE ARE @dt DATETIME

    SET @dt = '05/10/2008'

    DECLARE @mon INT

    DECLARE @sun INT

    SET @mon = 0

    SET @sun = 0

     

    WHILE @dt <= '05/31/2008'

    BEGIN

     

    IF (SELECT DATEPART(DW, @dt)) = 1

    BEGIN

    SET @sun = @sun +1

    END

     

    IF (SELECT DATEPART(DW, @dt)) = 2

    BEGIN

    SET @mon = @mon +1

    END

     

    SET @dt = DATEADD(DAY, +1, @dt)

    END

     

    SELECT @sun AS Sunday,

           @mon AS Monday

     

     

     

    • Marked as answer by Kalman Toth Saturday, August 7, 2010 5:32 AM
    Tuesday, May 13, 2008 3:46 PM
  • Hi David,

    May i know what you mean by set based approach ?

    can you give me an example if possible?

     

     

    Tuesday, May 13, 2008 3:54 PM
  • In your example you could possibly create a common table expression that does an aggregate count and order the results then a query after that that further aggregates or presents the result set.  You could also use table variables or temporary tables to insert results into and then continue to aggregate into another table variable or temp table until you have the data in a form that a single query can return the result set. 

    Tuesday, May 13, 2008 3:59 PM
  •  BFL wrote:

    My situation is  like this:

     

    Inside a cursor , I get the count of fields in a table based on a condition.
    Now i need to loop that many times and calculate a yes/no condition and get the counts of YES and NOs.

     

    This prompted me to go for a for loop.

     

     

     

     

    Here is a good article discussing using a 'numbers' table to eliminate cursors and loops...

    http://www.sqlservercentral.com/articles/TSQL/62867/

     

    If you give some sample input data and the desired output it would be easier to recommend a solution

    Tuesday, May 13, 2008 6:51 PM
    Answerer
  • If you're counting the number of fields in tables, perhaps the INFORMATION_SCHEMA.COLUMNS view may be of use, perhaps with some form of CASE Statement.

    Code Snippet

    SELECT TABLE_NAME, COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    GROUP BY TABLE_NAME


    Just a thought but as Anthony suggests it'll be much easier if you give an example of the data with DML and DDL if possible.

    HTH!
    Tuesday, May 13, 2008 7:19 PM
  • -----------------------------------------------------------

    declare

     

    @a int,@b int

     

     

    @a=1

    while

     

    (@a<=10)

    begin

    print

     

    @a

    set

     

    @a=@a+1

    end

    -----------------------------------------

    cute example

    'saapidu' which means eat it

    select

    Wednesday, July 21, 2010 8:46 AM
  • see what i have done for this situation

    DECLARE @FROMDATE DATETIME
    DECLARE @TODATE DATETIME
    declare @histdate datetime


    SET @FROMDATE='2010-07-05 00:00:00'
    SET @TODATE= '2010-07-05 00:00:00'
    set @histdate = @FROMDATE
    TEST:
     IF @HISTDATE<=@TODATE
      BEGIN 

       SET @HISTDATE=DATEADD(DAY,1,@HISTDATE) -- ADDING ONE DATE
       GOTO TEST -- IF DAYS REMAINING THEN CALCULATE FOR ONE MORE DAY
      END

    • Marked as answer by Kalman Toth Saturday, August 7, 2010 5:34 AM
    Wednesday, July 21, 2010 11:04 AM
  • Unlike in algorithmic programming languages, you need to "loop" very rarely in T-SQL because looping on unordered sets is built into the SQL language. It is called set-based operations. In the following demo, the database engine loops through the entire Product table with 504 rows without explicit looping command:

    -- SET BASED OPERATION example
    SELECT	FinishedGood=CASE	WHEN FinishedGoodsFlag=1 
    							THEN'Yes'ELSE'No'END, 
    		ItemCount=COUNT(*)
    FROM AdventureWorks2012.Production.Product
    GROUP BY FinishedGoodsFlag
    ORDER BY FinishedGoodsFlag DESC;
    
    /* FinishedGood	ItemCount
    	Yes			295
    	No			209
    */

    Certain tasks require looping, for example, index REBUILD for all tables in a database:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by Naomi N Sunday, August 8, 2010 4:13 AM
    • Marked as answer by Kalman Toth Thursday, September 27, 2012 7:28 PM
    • Edited by Kalman Toth Thursday, September 27, 2012 7:33 PM
    Saturday, August 7, 2010 5:43 AM
  • declare @i int
    while (@i<10)
    begin
    insert into dbname.dbo.table_name values @i
    @i=@i+1
    end

    Thursday, April 25, 2013 7:40 AM