[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Can I loop a stored procedure in SQL?

Here is my PROC.
My Goal is to run this for 6 consecutive months.
[dbo].[PROC_PM_Pipe_Main] is the main and will have the starting month and end date.

All the other PROC_PM_Pipe_Months need to run 6 consectutive months.  I set up 2 separate PROCS.  Main creates the table and Months inserts into the main table.
Both Scripts are the same, but I need to loop the entire select statement to start again.

If there is an easier way to do this, please let me know.


------------------------------------------------------------
USE [corporate_reporting]

DROP TABLE [corporate_reporting].dbo.tbl_PM_pipeline_scrub_all

EXEC      [dbo].[PROC_PM_Pipe_Main]
            @EffDate = '5-31-2011',  ---Starting Month
            @EffDate2 = '6-30-2011'
            
EXEC      [dbo].[PROC_PM_Pipe_Months]
            @EffDate = '6-30-2011',  
            @EffDate2 = '7-31-2011'
            
EXEC      [dbo].[PROC_PM_Pipe_Months]
            @EffDate = '7-31-2011',
            @EffDate2 = '8-31-2011'
            
EXEC      [dbo].[PROC_PM_Pipe_Months]
            @EffDate = '8-31-2011',
            @EffDate2 = '9-30-2011'


SELECT *
FROM  [corporate_reporting].dbo.tbl_PM_pipeline_scrub_all


0
gracie1972
Asked:
gracie1972
  • 3
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Might be easier to add some logic to _Main to accept #months as a parameter and have it call the _Months as many times as needed.
0
 
gracie1972Author Commented:
Here is my original script, can you show me an example for me to understand what you mean?



USE [corporate_reporting]
GO
/****** Object:  StoredProcedure [dbo].[PROC_PM_Pipe_Months]    Script Date: 10/25/2011 13:39:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PROC_PM_Pipe_Months]

(
@EffDate AS datetime,  
@EffDate2 AS datetime 
)

AS

/**************************************************************************************************
Created by				:Angela 
Date					:	10/26/2011	
Report Name				:PROC_PM_Pipeline_Months
Deployed Report Name	:	Pipeline Script
Parameters				:	 
Unit Test				:	
Busines Contact			:	
Notes					:	

Modification Log
Modified By			Modified Date		Comments
																		
---------------------------------------------------------------------------------------------------
Angela 	          10/26/2011			Create Procedure
---------------------------------------------------------------------------------------------------
----PIPELINE PULLTHROUGH AND DAYS - LIQUIDATION
----FROM BORROW DOC COLLECTION TO FINAL DOC COLLECTION STEP1
----NOTE: CHANGE DATE RANGE IN 2 PLACES 

---------------------------------------------------------------------------------------------------------*/
-----PIPELINE TABLE

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

IF OBJECT_ID('tempdb..#pipeline_history') IS NOT NULL 
DROP TABLE  #pipeline_history

SELECT 
acct_id, 
max(End_Date) end_date,
--max (convert(smalldatetime,convert(varchar(10),ref_start_date,101))) as ref_start_date,
service_transfer_date,
lwo_rfrl_typ,
case
	when pipeline_step = 'Borrower Document Collection' then '1'
	when pipeline_step = 'Deal Underwriting' then '1'
	else '' end as pipeline_step
      
INTO #pipeline_history
FROM corporate_reporting.dbo.lwo_pipeline
WHERE pipeline_step IN ('Borrower Document Collection','Deal Underwriting')
--('Deal Approval','Doc Production','Final Doc Collection')
AND lwo_rfrl_typ --= 'HAMP Trial Modification'
--IN ('AltMod2011 Trial Modification','Apollo Trial Modification','HAMP Trial Modification','Mod 24 Trial Modification',
--'Payment Reduction Plan','Alt Mod','Modification','Payment Plan','Forbearance Plan')
IN ('Regular Short Sale', 'HAFA Short Sale','Regular Deed for Lease', 'HAFA Deed for Lease', 'Regular Deed In Lieu', 'HAFA Deed In Lieu','Deed-In-Lieu - Delegated')

AND end_date between @EffDate and @EffDate2
group by
acct_id, 
service_transfer_date,
lwo_rfrl_typ,
case
	when pipeline_step = 'Borrower Document Collection' then '1'
	when pipeline_step = 'Deal Underwriting' then '1'
	else '' end 

IF OBJECT_ID('tempdb..#pipeline_scrub') IS NOT NULL 
DROP TABLE  #pipeline_scrub

select min(end_date) end_date1, acct_id, service_transfer_date, lwo_rfrl_typ
into #pipeline_scrub
from #pipeline_history

group by acct_id, service_transfer_date, lwo_rfrl_typ
order by acct_id

------------------------------------------------------
IF OBJECT_ID('tempdb..#pipeline_history2') IS NOT NULL 
DROP TABLE  #pipeline_history2

SELECT 
acct_id, 
max(End_Date) end_date,
--max (convert(smalldatetime,convert(varchar(10),ref_start_date,101))) as ref_start_date,
service_transfer_date,
lwo_rfrl_typ,
case
	when pipeline_step = 'Deal Approval' then '2'
	when pipeline_step = 'Doc Production' then '2'
	when pipeline_step = 'Final Doc Collection' then '2'
	else '' end as pipeline_step
      
INTO #pipeline_history2
FROM corporate_reporting.dbo.lwo_pipeline
WHERE pipeline_step IN --('Borrower Document Collection','Deal Underwriting')
('Deal Approval','Doc Production','Final Doc Collection')
AND lwo_rfrl_typ --= 'HAMP Trial Modification'
--IN ('AltMod2011 Trial Modification','Apollo Trial Modification','HAMP Trial Modification','Mod 24 Trial Modification',
--'Payment Reduction Plan','Alt Mod','Modification','Payment Plan','Forbearance Plan')
IN ('Regular Short Sale', 'HAFA Short Sale','Regular Deed for Lease', 'HAFA Deed for Lease', 'Regular Deed In Lieu', 'HAFA Deed In Lieu','Deed-In-Lieu - Delegated')


AND end_date > @EffDate

group by
acct_id, 
service_transfer_date,
lwo_rfrl_typ,
case
	when pipeline_step = 'Deal Approval' then '2'
	when pipeline_step = 'Doc Production' then '2'
	when pipeline_step = 'Final Doc Collection' then '2'
	else '' end

IF OBJECT_ID('tempdb..#pipeline_scrub2') IS NOT NULL 
DROP TABLE  #pipeline_scrub2

select min(end_date) end_date2, acct_id, service_transfer_date, lwo_rfrl_typ
into #pipeline_scrub2
from #pipeline_history2

group by acct_id, service_transfer_date, lwo_rfrl_typ
order by acct_id

-------------------------------------------------------------------------------
---FINAL
INTO [corporate_reporting].dbo.tbl_PM_pipeline_scrub_all
select  
ph.acct_id, 
ph.service_transfer_date,
ph.lwo_rfrl_typ				AS lwo_rfrl_typ, 
ph.end_date1,
ph2.lwo_rfrl_typ			AS ph2_lwo_rfrl_typ, 
ph2.end_date2,
--case
--	when ph.ref_start_date < = ph2.end_date then 'next_step'
--	else 'no_move' end as movement,
case
	when ph2.lwo_rfrl_typ is null then 'match'
	when ph.lwo_rfrl_typ = ph2.lwo_rfrl_typ then 'match'
	when ph.lwo_rfrl_typ <> ph2.lwo_rfrl_typ then 'nomatch'
	else '' end as rfrl_match,
datediff(d,ph.end_date1,ph2.end_date2) as days,
case
	when datediff(d,ph.end_date1,ph2.end_date2) < 0 then 'neg_days'
	when datediff(d,ph.end_date1,ph2.end_date2) >= 0 then 'pos_days'
	else '' end as days2

from #pipeline_scrub ph
left join #pipeline_scrub2 ph2 on ph2.acct_id = ph.acct_id
----where ph.ref_start_date < ph2.end_date 
----and ph.lwo_rfrl_typ = ph2.lwo_rfrl_typ
order by ph.acct_id


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

Open in new window

0
 
Scott PletcherSenior DBACommented:
CREATE PROCEDURE [dbo].[PROC_PM_Pipe_Main] (
            @EffDate datetime,
            @EffDate2 datetime,
            @NumOfMonths smallint
)
AS
DECLARE @monthCounter smallint
...existing code as it is now...

SET @monthCounter = 2
WHILE @monthCounter <= @NumOfMonths
BEGIN
    SET @EffDate = DATEADD(MONTH, 1, @EffDate)
    SET @EffDate2 = DATEADD(MONTH, 1, @EffDate)
    EXEC [dbo].[PROC_PM_Pipe_Months] @EffDate, @EffDate2
    SET @monthCounter = @monthCounter + 1
END --WHILE

GO
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LowfatspreadCommented:
why are you asking essentially the same question again?

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27414964.html

please respond to your original question...
0
 
gracie1972Author Commented:
I tried a PROC instead to see if it would make sense to people :-)
0
 
LowfatspreadCommented:
@scott

  SET @EffDate2 = DATEADD(MONTH, 1, @EffDate)

doesn't preserve the actual end date of the month which is what is intended....

e.g Jan 31
      feb 28
      mar 28
      apr 28 etc...
0
 
LowfatspreadCommented:
@gracie

no i don't think it really does , help as its more difficult to automate without dynamic sql...

what we really require is an explanation of the underlying code ...
what are you trying to achieve...
what is the end result supposed to be / how you wish to use it...

some example "input" data and the expected results would assist...
0
 
Scott PletcherSenior DBACommented:
>> doesn't preserve the actual end date of the month which is what is intended.... <<

Actually if it starts out on the 31st, it does.  Viz:

SELECT DATEADD(MONTH, 1, '20110131')
SELECT DATEADD(MONTH, 2, '20110131')
SELECT DATEADD(MONTH, 3, '20110131')
SELECT DATEADD(MONTH, 4, '20110131')
SELECT DATEADD(MONTH, 5, '20110131')
SELECT DATEADD(MONTH, 6, '20110131') ...

But, yes, to be genuinely accurate, you will need to make sure you get the last day of the month.

SET @monthCounter = 2
WHILE @monthCounter <= @NumOfMonths
BEGIN
    SET @EffDate = DATEADD(DAY, -DAY(@EffDate), DATEADD(MONTH, 2, @EffDate))
    SET @EffDate2 = DATEADD(DAY, -DAY(@EffDate2), DATEADD(MONTH, 2, @EffDate))
    EXEC [dbo].[PROC_PM_Pipe_Months] @EffDate, @EffDate2
    SET @monthCounter = @monthCounter + 1
END --WHILE
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now