Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL: Alternative to WHILE LOOP

Posted on 2013-11-14
1
Medium Priority
?
562 Views
Last Modified: 2014-01-31
Hello all,

I have inherited a proc that is causing the DBAs headaches.  Specifically, there is a portion of the code that declares variables and then loops through a staging table while setting a counter.

I would like to rewrite this section to be more efficient, but don't have much experience with these sorts of things; could a CROSS APPLY work in this scenario?  What is the "best practice" for this sort of thing?

Here's the code:

DECLARE @MAXMONTHS INT
      SELECT 
            @MAXMONTHS = MAX(DATEDIFF(MM,STARTDATE,ENDDATE)) + 1 
      FROM 
            [Stage].[RESULTS] -- ADD 1 IN ORDER TO ACCOUNT FOR ROUNDING DOWN IN YEAR CALCULATION

DECLARE @THISMONTH INT
SET @THISMONTH = 1
WHILE (@THISMONTH <= @MAXMONTHS)
BEGIN
      INSERT INTO 
            [Stage].[TPHASES]
      SELECT
            R.SF_ID
            , R.AP_ID
            , @THISMONTH AS Phase
            , DATEADD(MM,@THISMONTH-1,R.STARTDATE) AS STARTDATE
            , DATEADD(D,-1,DATEADD(MM,@THISMONTH,R.STARTDATE)) AS ENDDATE
      FROM 
            [Stage].[RESULTS] AS R
            INNER JOIN 
            [Stage].[RESULTSWO] AS RWO 
            ON RWO.SF_ID = R.SF_ID AND RWO.[ROW] = 1
      WHERE 
            R.[ROW] = 1
            AND RWO.ENDDATE >= DATEADD(MM,@THISMONTH-1,R.STARTDATE)
      ORDER BY
            R.SF_ID
      SET 
            @THISMONTH = @THISMONTH + 1
END
;

Open in new window


Please let me know what other information I can provide.  Thanks.

P.S. We're on 2008 R2.  (For now.)
0
Comment
Question by:Donovan Moore
1 Comment
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39649586
Please note that there must be a reason for this. You see the procedure is trying to insert the data month by month, apparently minimising the impact on your database transaction log.

If it done it all at once it would start a huge transaction (depending on the number of rows in your tables) and would block everyone who is accessing these tables.

BTW, to remove the WHILE loop all you have to do is to adjust the script a little bit
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question