游标及临时表存储过程实例

CREATE  PROCEDURE [dbo].[ComparisonOfContinuousTime_Multiple_DG_ALL_TypeAll]
    @UserInfoID AS VARCHAR(21),
    @QuantityOrSale AS VARCHAR(50),
    @Day_From AS TINYINT,
    @Day_To AS TINYINT,
    @Year SMALLINT
AS


    -- 判断闰年/小月
    declare @Day_From_Tmp as TINYINT
    declare @Day_To_Tmp as TINYINT


    -- 创建一个临时表
    CREATE TABLE #ComparisonOfContinuousTime
    (
        m SMALLINT,    -- 月份
        v FLOAT    -- 值
    )

    DECLARE @BusinessInfoCode VARCHAR(21)
    declare @unitID int
    select @unitID=fld_UnitID from t_UserInfo where fld_ID=@UserInfoID
    DECLARE MyCursor CURSOR FOR
        SELECT fld_Code FROM dbo.T_BusinessInfo WHERE fld_RegulatorID in (select fld_ID from T_UserInfo where fld_UnitID in(select fld_ID from T_UnitInfo where fld_HigherID=@unitID))

    OPEN MyCursor

    FETCH NEXT FROM MyCursor INTO @BusinessInfoCode
    WHILE @@FETCH_STATUS = 0
    BEGIN
        

        -- 声明循环因子
        DECLARE @m SMALLINT
        SET @m = 1
    
        -- 建立循环体
        WHILE (@m <= 12)
        BEGIN


            set @Day_From_Tmp = @Day_From
            set @Day_To_Tmp = @Day_To

            -- 判断闰年
            if @m = 2
            begin
                if dbo.LongYear(@Year) = 1
                begin
                    if @Day_From > 29
                        set @Day_From_Tmp = 29
                    if @Day_To > 29
                        set @Day_To_Tmp = 29
                end
                else
                begin
                    if @Day_From > 28
                        set @Day_From_Tmp = 28
                    if @Day_To > 28
                        set @Day_To_Tmp = 28
                end
            end

            -- 判断小月
            if @m in (4, 6, 9, 11)
            begin
                if @Day_From > 30
                    set @Day_From_Tmp = 30
                if @Day_To > 30
                    set @Day_To_Tmp = 30
            end



            -- 向临时表赋值
            INSERT INTO #ComparisonOfContinuousTime
            VALUES (
                -- 年份
                @m, 
                -- 当年指定时间范围的销量或销售金额
                dbo.ContrastTheSamePeriod_GetOneYear(
                    CAST(
                        CAST(@Year AS VARCHAR(4))
                        + '-' + CAST(@m AS VARCHAR(2))
                        + '-' + CAST(@Day_From_Tmp AS VARCHAR(2))
                    AS DATETIME),
                    CAST(
                        CAST(@Year AS VARCHAR(4))
                        + '-' + CAST(@m AS VARCHAR(2))
                        + '-' + CAST(@Day_To_Tmp AS VARCHAR(2))
                    AS DATETIME),
                    @BusinessInfoCode,
                    '普通',
                    @QuantityOrSale)
                +
                dbo.ContrastTheSamePeriod_GetOneYear(
                    CAST(
                        CAST(@Year AS VARCHAR(4))
                        + '-' + CAST(@m AS VARCHAR(2))
                        + '-' + CAST(@Day_From_Tmp AS VARCHAR(2))
                    AS DATETIME),
                    CAST(
                        CAST(@Year AS VARCHAR(4))
                        + '-' + CAST(@m AS VARCHAR(2))
                        + '-' + CAST(@Day_To_Tmp AS VARCHAR(2))
                    AS DATETIME),
                    @BusinessInfoCode,
                    '一二类',
                    @QuantityOrSale))
            -- 循环因子自增
            SET @m = @m + 1
        END
        
        FETCH NEXT FROM MyCursor INTO @BusinessInfoCode

    END
    
    CLOSE MyCursor
    DEALLOCATE MyCursor

    -- 将临时表返回给调用者
--    SELECT m,SUM(v) as v FROM #ComparisonOfContinuousTime GROUP BY m
SELECT  m,SUM(v) as v FROM #ComparisonOfContinuousTime GROUP BY m order by m

GO

CREATE          FUNCTION ContrastTheSamePeriod_GetOneYear
    (@From DATETIME, @To DATETIME, @BusinessInfoCode VARCHAR(21), @Type VARCHAR(50), @QuantityOrSale VARCHAR(50))  
RETURNS FLOAT AS  
BEGIN 

    DECLARE @Result FLOAT
    set @Result = 0

    IF @QuantityOrSale = 'Quantity'
    BEGIN
        SELECT @Result = sum(fld_QuantityOfSale)
        FROM dbo.T_Sampling
        WHERE fld_BusinessInfoCode = @BusinessInfoCode
            AND fld_Type = @Type
            AND fld_Time >= @From
            AND fld_Time <= @To
    END
    ELSE IF @QuantityOrSale = 'Sale'
    BEGIN
        SELECT @Result = sum(fld_ValueOfSale)
        FROM dbo.T_Sampling
        WHERE fld_BusinessInfoCode = @BusinessInfoCode
            AND fld_Type = @Type
            AND fld_Time >= @From
            AND fld_Time <= @To
    END

    if @Result is null
        set @Result = 0

    -- 返回结果
    RETURN @Result

END