SP 存储过程随笔记录（1）

admin 管理员组

文章数量: 1087652

SP 存储过程随笔记录（1）

USE [CRM_CN2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*********************************************************************************************************
--建立者: HERMAN  日期﹕2020-06-28
--調用的程序﹕
--說明﹕Enzo star权益
--更新記錄﹕
--		日期			    更改人				   更新說明
------------------	     -------------			--------------------------------------------
--  2020-06-28             HERMAN                   新增
----*********************************************************************************************************/
--EXEC [SP_CRM_AUTO_ENZO_STAR_POWER]
CREATE PROCEDURE [dbo].[SP_CRM_AUTO_ENZO_STAR_POWER]
AS
BEGINDECLARE @Tran_Date DATETIME = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()));DECLARE @Error_No INT = 0;DECLARE @Error_Msg NVARCHAR(500) = N'';--Enzo貨品IF OBJECT_ID('tempdb..#TempIncludeStockType') IS NOT NULLDROP TABLE #TempIncludeStockType;   --購買ENZO貨品消費換積分及生日月送1倍積分IF OBJECT_ID('tempdb..#tbCRM_EnzoStar_Credit_History') IS NOT NULLDROP TABLE #tbCRM_EnzoStar_Credit_History;SELECT DISTINCT stock_type,stock_groupINTO #TempIncludeStockTypeFROM   dbo.[stock nature]WHERE  product_line = 10;--消費獎分(售價1:1積分 壹年有效期)SELECT  CCH.*,CM.Birthday,CAST(CCH.Actual_Selling_Price AS INT) AS EnzoStar_Credit, DATEADD(SECOND,86399,DATEADD(DAY,365,CONVERT(VARCHAR(10),CCH.Tran_Date,23)))  AS Credit_Expiry_DateINTO    #tbCRM_EnzoStar_Credit_HistoryFROM    tbCRM_Credit_History AS CCH WITH ( NOLOCK )    LEFT JOIN tbCRM_Credit_History AS CCH10 WITH ( NOLOCK ) ON CCH10.CRM_Domain = CCH.CRM_DomainAND CCH10.Customer_ID = CCH.Customer_IDAND CCH10.Stock_Group = CCH.Stock_GroupAND CCH10.Stock_Type = CCH.Stock_TypeAND CCH10.Stock_Create_Domain = CCH.Stock_Create_DomainAND CCH10.Stock_No = CCH.Stock_NoAND CCH10.Stock_Sub_No = CCH.Stock_Sub_NoAND CCH10.Tran_Date > CCH.Tran_DateAND CCH10.Tran_Code = 10INNER JOIN tbCRM_Customer_Tag AS Ta WITH ( NOLOCK )ON Ta.CRM_Domain = CCH.CRM_DomainAND Ta.Customer_ID = CCH.Customer_IDAND Ta.Tag_ID = 23 --Enzo StarAND Ta.Status_Flag=0INNER JOIN #TempIncludeStockType S ON S.stock_group = CCH.Stock_GroupAND S.stock_type = CCH.Stock_Type INNER JOIN dbo.tbCRM_Customer_Master AS CM WITH (NOLOCK)ON CM.CRM_Domain = CCH.CRM_Domain AND CM.Customer_ID = CCH.Customer_IDWHERE   CCH.Tran_Code = 1AND CCH.Tran_Date >= @Tran_DateAND CCH.Tran_Date < CONVERT(DATE, GETDATE(),120)AND CCH10.Seq IS NULL;--生日獎分(額外贈送的1倍銷售積分3個月有效期)INSERT  #tbCRM_EnzoStar_Credit_History( Seq ,Tran_Date ,Tran_Code ,CRM_Domain ,Customer_ID ,Membership_Seq ,Card_No ,IC_Card_No ,Serial_No ,Credit ,Sales_ICS_Domain ,Sales_Branch_Code ,Sales_Order_No ,Sales_Item ,Stock_Group ,Stock_Type ,Stock_Create_Domain ,Stock_No ,Stock_Sub_No ,Stock_Out_Date ,Mould_ICS_Domain ,Mould_Type ,Mould_No ,Mould_Stock_Type ,Original_Selling_Price ,Actual_Selling_Price ,Selling_Price ,Consume_Price ,Coupon_Price ,Actual_Consume_Price ,VIP_Approved_By ,Discount ,Gift_Code ,Handler ,Remark ,Bar_Code ,Birthday ,EnzoStar_Credit ,Credit_Expiry_Date)SELECT  Seq ,Tran_Date ,Tran_Code ,CRM_Domain ,Customer_ID ,Membership_Seq ,Card_No ,IC_Card_No ,Serial_No ,Credit ,Sales_ICS_Domain ,Sales_Branch_Code ,Sales_Order_No ,Sales_Item ,Stock_Group ,Stock_Type ,Stock_Create_Domain ,Stock_No ,Stock_Sub_No ,Stock_Out_Date ,Mould_ICS_Domain ,Mould_Type ,Mould_No ,Mould_Stock_Type ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,Discount ,Gift_Code ,Handler ,Remark ,Bar_Code ,Birthday ,Credit AS EnzoStar_Credit ,DATEADD(SECOND, 86399,DATEADD(MONTH, 3, CONVERT(VARCHAR(10), Tran_Date, 23))) AS Credit_Expiry_DateFROM    #tbCRM_EnzoStar_Credit_HistoryWHERE   MONTH(Birthday) = MONTH(@Tran_Date)AND Credit <> 0--插入積分歷史中間表IF EXISTS (SELECT * FROM #tbCRM_EnzoStar_Credit_History)BEGINBEGIN TRYBEGIN TRANSACTION MyTransaction;DECLARE @Handler VARCHAR(20) = 'SYSTEM';INSERT INTO CRM_CN2_MD.dbo.tbCRM_Credit_History_Log (Seq_New,Seq,Create_Date,Status_ID,Tran_Date,Tran_Code,CRM_Domain,Customer_ID,Membership_Seq,Card_No,IC_Card_No,Serial_No,Credit,Sales_ICS_Domain,Sales_Branch_Code,Sales_Order_No,Sales_Item,Stock_Group,Stock_Type,Stock_Create_Domain,Stock_No,Stock_Sub_No,Stock_Out_Date,Mould_ICS_Domain,Mould_Type,Mould_No,Mould_Stock_Type,Original_Selling_Price,Actual_Selling_Price,Selling_Price,Consume_Price,Coupon_Price,Discount,Gift_Code,Handler,Remark,Credit_Expiry_Date)SELECT NEWID() Seq_New,0 Seq,@Tran_Date Create_Date,0 Status_ID,@Tran_Date Tran_Date,47 Tran_Code,CH.CRM_Domain,CH.Customer_ID,CH.Membership_Seq,CH.Card_No,CH.IC_Card_No,CH.Serial_No,CH.EnzoStar_Credit,CH.Sales_ICS_Domain,CH.Sales_Branch_Code,CH.Sales_Order_No,CH.Sales_Item,CH.Stock_Group,CH.Stock_Type,CH.Stock_Create_Domain,CH.Stock_No,CH.Stock_Sub_No,CH.Stock_Out_Date,CH.Mould_ICS_Domain,CH.Mould_Type,CH.Mould_No,CH.Mould_Stock_Type,CH.Original_Selling_Price,CH.Actual_Selling_Price,CH.Selling_Price,CH.Consume_Price,CH.Coupon_Price,CH.Discount,CH.Gift_Code,@Handler Handler,CH.Remark,CH.Credit_Expiry_DateFROM   #tbCRM_EnzoStar_Credit_History CHWHERE  CH.EnzoStar_Credit > 0;COMMIT TRANSACTION MyTransaction;END TRYBEGIN CATCHROLLBACK TRANSACTION MyTransaction;SELECT @Error_No = @@ERROR,@Error_Msg = ERROR_MESSAGE();RAISERROR(N'Enzo消費獎積分報錯,%s', 16, 1, @Error_Msg);END CATCH;END;END;

本文标签： SP 存储过程随笔记录（1）