gooliugle 原文 插入更新表内容的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <xxxx>
-- 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