Solved

SQL: Alternative to WHILE LOOP

Posted on 2013-11-14
1
444 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 500 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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