SQL，如何使用每个日期列表作为过程的参数来循环？

Question

我有一个接收特定日期作为参数的过程即Exec ProcDB'20150428'

我经常需要在多个日期运行此过程，通常我会重新输入 Exec ProcDB 'date1' GO Exec ProcDB 'date2'go.....我认为这不聪明，所以

我可以使用 Select Distinct [dates] From Table1 Order By [dates] 获取有效的日期列表。

所以我想创建一个接收 Start_Dt 和 End_Dt 的新过程它循环查找我选择的不同返回的所有日期，其中包括 Start_Dt 和 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

更新:

最终解决方案:

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

Answer 1

您将需要使用光标。我相信这是一个很好的资源:http://www.codeproject.com/Tips/277847/How-to-use-Cursor-in-Sql

我尝试用您提供的信息制作一个示例。

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