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

Question

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

Check out the answers [here](http://stackoverflow.com/questions/477064/is-it-possible-to-execute-a-stored-procedure-over-a-set-without-using-a-cursor). — zimdanen, Apr 29 '15 at 13:03

JMariña · Answer 1 · 2015-04-29T16:22:28.370

3

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

edited Apr 29 '15 at 16:22

answered Apr 29 '15 at 13:16

JMariña

324
2
15

hi, thanks. pls see my other part of the question related to the procedure as an update on original question. – Flib Apr 29 '15 at 13:53
If I understand correctly, you are saying that the parameter received by the sproc you use is a varchar, and the [date] column in your Prices table is a date time. If this is the case I recommend placing the cast function inside of the cursor to change the data type pulled from the Price table from Date to varchar. https://technet.microsoft.com/en-us/library/aa224021(v=sql.80).aspx CAST ( @date AS VARCHAR(15) ) – JMariña Apr 29 '15 at 14:22
I updated the response to show placement of the cast, also I had an inconsistency in my cursor name (showed as open sub) you will need to replace this with your cursor name as well in your response – JMariña Apr 29 '15 at 14:33
what is wrong here? Exec ProcessDB @Refdate = (CAST(@date as varchar(15)) ..it say expect Select – Flib Apr 29 '15 at 14:46
I apologize, I'm learning a bit about CAST myself. looks like we cannot use the cast directly as a parameter. What we can do is create another variable that is equal to the cast value. And use that for the parameter. Sorry about that, and hopefully this gets you through! Your question has led to some great learning for me :) Please ignore the ' in front of the '@'. I need this formatting for the comment. DECLARE '@date2 VARCHAR(15) SET '@date2 = (CAST ('@date AS varchar(15))) Exec ProcdB date_parameter_name = '@date2 – JMariña Apr 29 '15 at 15:17

score 2 · Accepted Answer · edited May 23 '17 at 11:43

2

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.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 29 '15 at 13:16

Zohar Peled

79,642
10
69
121

hi, thanks. pls see my other part of the question related to the procedure as an update on original question. – Flib Apr 29 '15 at 13:53
sub replaced. and reset @date as Date but converto not working in this way: Exec ProcessDB Convert(varchar(15),@date) – Flib Apr 29 '15 at 14:42
@Flib: You need to specify the style in convert; i.e if you wish to convert to `dd/MM/yyyy` format you need to write `Convert(varchar(10), @date, 103)`. what is the format you use for date? – Zohar Peled Apr 29 '15 at 14:47
got it, I need YYYMMDD, I think its 112. will try – Flib Apr 29 '15 at 14:53
@Flib: yes, that's 112. – Zohar Peled Apr 29 '15 at 14:54
it say incorrect syntax near convert on: Exec ProcessDB Convert(varchar(15), @date, 112) – Flib Apr 29 '15 at 14:56
You can't convert it there, you need to declare the `@date` variable as `varchar(15)` and use the convert in the select statement of the cursor: `Select Distinct Convert(varchar(15), [date], 112)`.... – Zohar Peled Apr 29 '15 at 15:02

score 1 · Answer 3 · answered Apr 29 '15 at 13:33

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

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

3 Answers3