CODEWITHSUNDEEP
sqlloopsstored-proceduressql-server-2014-express
Flib
Flib

Reputation: 175

SQL, How to loop for a date list using each as parameter for an Procedure?

I have an Procedure that receives an specific date as parameter ie Exec ProcDB '20150428'

frequently I need to run this procedure for many dates and usually I retype Exec ProcDB 'date1' GO Exec ProcDB 'date2'go..... I think it's not smart, so

I can get the valid list of dates using a Select Distinct [dates] From Table1 Order By [dates].

So I want to create a new Procedure that receives Start_Dt and End_Dt and it loops for all dates that my select distinct returns where its between including Start_Dt and End_Dt.

ie something like:

Create ProcDBlist Start_Dt as date, End_Dt as date
For each date in: Select Distinct [date] from [table1] where [date] >= @Start_Dt and [date] <= @End_dt
Do: Exec ProcDB 'Date n'
End

UPDATED:

Final solution:

Create procedure [dbo].[ProcessDBRange] (@Start_dt as varchar(15) =null, @End_dt as varchar(15) =null)
As
Begin
DECLARE @date as varchar(15)

DECLARE Cursor_ProcessDB CURSOR FOR
    Select Distinct Convert(varchar(15), [date], 112) as [date]
    From [Prices]
    Where [date] >= @Start_dt and [date] <= @End_dt
    Order By [date]

OPEN Cursor_ProcessDB

FETCH next FROM Cursor_ProcessDB
INTO @date

WHILE @@FETCH_STATUS = 0

BEGIN

Exec ProcessDB @date

FETCH next FROM Cursor_ProcessDB
INTO @date

END
CLOSE Cursor_ProcessDB
DEALLOCATE Cursor_ProcessDB
End

Upvotes: 2

Views: 8376

Answers (3)

JMari&#241;a
JMari&#241;a

Reputation: 324

You will want to use a cursor. I believe this is a good resource: http://www.codeproject.com/Tips/277847/How-to-use-Cursor-in-Sql

I tried to make an example with the info you provided.

DECLARE @Start_dt DATE;
DECLARE @End_dt DATE;
DECLARE @date DATE;

DECLARE cursor_name CURSOR FOR
    SELECT DISTINCT Date
    FROM [table1]
    WHERE Date >= @Start__Dt 
        and Date <= @End__Dt
    ORDER BY Date

OPEN cursor_name

FETCH next FROM cursor_name
INTO @date

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @date2 VARCHAR(15)
SET @date2 = (CAST ( @date AS varchar(15) ))

Exec ProcdB date_parameter_name = @date2     

FETCH next FROM cursor_name
INTO @date

END
CLOSE cursor_name
DEALLOCATE cursor_name

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82484

This can be accomplished by using a cursor.
Basically, it goes like this:

DECLARE @Date datetime -- a local variable to get the cursor's result

DECLARE DatesCursor CURSOR FOR
  Select Distinct [dates] where [dates] between @Start_Dt and @End_Dt From Table1 Order By [dates]. -- the query that the cursor iterate on

OPEN DatesCursor
FETCH NEXT FROM DatesCursor INTO @Date 
WHILE @@FETCH_STATUS = 0 -- this will be 0 as long as the cursor returns a result
  BEGIN
    Exec ProcDB @Date
    FETCH NEXT FROM DatesCursor INTO @Date -- don't forget to fetch the next result inside the loop as well!
  END
-- cleanup - Very important!
CLOSE DatesCursor 
DEALLOCATE DatesCursor

Edit
I've just read the link that zimdanen gave you in the comments, I must say I think in this case it may be better than a using a cursor.

Edit #2

First, change OPEN sub to OPEN cursor_name. Second, use CONVERT to get the date as a string. Make sure you convert with the correct style, otherwise you are prone to get incorrect dates and/or exceptions.

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can do it with cursor. Also you can alter proc to receive 2 parameters @sd date, @ed date then in proc do a loop:

alter procedure procDB
@sd date,
@ed date
as
begin
    while @sd <= @ed
    begin
        --do your staff

        set @sd = dateadd(dd, 1, @sd)
    end    
end

Upvotes: 1

Related Questions