游标使用

DECLARE PeriodKey_cursor CURSOR FOR

select Period_Key,[Year],[MonthName] from dbo.Period_DIM_Backup where Period_Key >= cast(@FiscalYear-1 AS varchar(4))+'0701' and Period_Key <= cast(@FiscalYear AS varchar(4))+'0630' ORDER BY Period_Key asc

OPEN PeriodKey_cursor

-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.

FETCH NEXT FROM PeriodKey_cursor INTO @Period_Key,@Year,@MonthName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- Get FiscalMonthId.

if @Year < @FiscalYear

set @FiscalMonthId= DATEPART(month, cast(@Period_Key as varchar(8)))-6

else

set @FiscalMonthId = DATEPART(month, cast(@Period_Key as varchar(8))) + 6

-- Get FiscalQuarterID

if @FiscalMonthId>=1 and @FiscalMonthId<=3

set @FiscalQuarterID=1

else if @FiscalMonthId>=4 and @FiscalMonthId<=6

set @FiscalQuarterID=2

else if @FiscalMonthId>=7 and @FiscalMonthId<=9

set @FiscalQuarterID=3

else

set @FiscalQuarterID=4

-- Get FiscalWeekID

 

PRINT 'Author: ' + cast(@Period_Key as varchar(8))+','+cast(@Year as varchar(4))+','+@MonthName+','+cast(@FiscalMonthId as varchar(4))+','+cast(@FiscalQuarterID as varchar(4))

 

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM PeriodKey_cursor INTO @Period_Key,@Year,@MonthName

END

CLOSE PeriodKey_cursor

DEALLOCATE PeriodKey_cursor