插入更新表内容的存储过程

时间：2011-03-15 来源：gooliugle

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <liuwei>
-- Create date: <2011-03-15>
-- Description:    <道具系统日报表统计>
-- =============================================
ALTER PROCEDURE [dbo].[sp_PropsReport_DaySync]

AS
BEGIN

--循环读取TbApps里的数据
--声明游标
DECLARE cursor_App CURSOR
FOR
SELECT Id FROM TbApps

--打开游标
OPEN cursor_App
DECLARE @AppId int

DECLARE @ReportDay VARCHAR(10), @OrderCount INT, @ActAmt INT,@PersonCount INT

SELECT @ReportDay=CONVERT(VARCHAR(10),DATEADD(dd, -1, GETDATE()),120)

--统计TbPropsOrder里的Status=2的所有已扣费数据
FETCH NEXT FROM cursor_App INTO @AppId
WHILE @@fetch_status=0
BEGIN
    SET @ActAmt=0
    SET @OrderCount=0
    SET @PersonCount=0

    SELECT @ActAmt=ISNULL(SUM(ActPay),0), @OrderCount=COUNT(1),@PersonCount=COUNT(DISTINCT SndaId)
    FROM TbPropsOrder(NOLOCK)
    WHERE AppId=@AppId AND Status=2
    AND LogTime>=@ReportDay AND LogTime<CONVERT(VARCHAR(10),GETDATE(),120)

    IF EXISTS(SELECT 1 FROM TbPropsOrderDayReport(NOLOCK) WHERE ReportDay=@ReportDay AND AppId=@AppId)
        UPDATE TbPropsOrderDayReport
        SET OrderCount=@OrderCount, ActAmt=@ActAmt ,PersonCount = @PersonCount
        WHERE ReportDay=@ReportDay AND AppId=@AppId
    ELSE
        INSERT TbPropsOrderDayReport(ReportDay, OrderCount, ActAmt, AppId,PersonCount)
        VALUES(@ReportDay, @OrderCount, @ActAmt, @AppId,@PersonCount)

    FETCH NEXT FROM cursor_App INTO @AppId
END

--关闭游标
CLOSE cursor_App
--释放游标
DEALLOCATE cursor_App

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

相关阅读更多 +