存储过程实例总结(开发中的错误与总结,调试,数据库函数DATEDIFF计算当前日期是否在本周内)

USE [POND]
GO
/****** Object:  StoredProcedure [dbo].[OrderChargeList]    Script Date: 04/16/2014 13:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[OrderChargeList]
AS
DECLARE @dateDiffValue int,
@cnt int
BEGIN
    SET NOCOUNT ON;

    SELECT @dateDiffValue= DATEDIFF(WEEK,isnull((SELECT TOP 1 CONVERT(VARCHAR(10),EnteredDate,121) FROM dbo.LGTobeOrder_CCBatch),'2000-01-01'),GETDATE())
    
    --SELECT @cnt= COUNT(*) FROM dbo.LGTobeOrder_CCBatch
    --SELECT * FROM LGOrder
    --where customerid is not null and orderid is not null
    --select * from dbo.LGTobeOrder_CCBatch
    
    --delete  FROM LGOrder where customerid not in(17,19,1)

    --IF(@cnt<0)
    --BEGIN
    --    INSERT INTO dbo.LGTobeOrder_CCBatch(CustomerID,orderID,FirstName,LastName,recordCount,perRecordCost,ccAmount,EMail)
    --    SELECT a.customerid,a.orderid,b.firstName,b.lastName, dbo.[GetCustomerDataCount](a.orderid),0.65,6.5,b.EmailAddress FROM LGOrder a inner join LGCustomerBilling b
    --    ON a.customerid =b.customerid
        
    --    SELECT * FROM dbo.LGTobeOrder_CCBatch WHERE ccProcStatus != 'DONE'
    --    RETURN
    --END
    
    --IF(@dateDiffValue<7 AND @dateDiffValue>=0)
    IF(@dateDiffValue>7)
    BEGIN
    /*print 'a'*/
        --SELECT * FROM dbo.LGTobeOrder_CCBatch WHERE ccProcStatus != 'DONE'
        truncate table dbo.LGTobeOrder_CCBatch
        
        INSERT INTO dbo.LGTobeOrder_CCBatch(CustomerID,orderID,FirstName,LastName,recordCount,perRecordCost,ccAmount,EMail)
        SELECT a.customerid,a.orderid,b.firstName,b.lastName, dbo.[GetCustomerDataCount](a.orderid) as 'recordCount',0.65,dbo.[GetCustomerDataCount](a.orderid)*0.65,b.EmailAddress FROM LGOrder a inner join LGCustomerBilling b
        ON a.customerid =b.customerid
    END
    
    SELECT * FROM dbo.LGTobeOrder_CCBatch WHERE isnull(ccProcStatus,'') != 'DONE'
    
    --select top 10 * from dbo.LGOrder
    --select top 10 * from dbo.LGOrderZips
    --SELECT top 10 [ZipFrom],[ZipTo],[Radius]  FROM [POND].[dbo].[zipcode_radius_around]
    --where zipfrom =12601 and radius<=10
    --1 准备数据
    SET NOCOUNT OFF
END

USE [POND]
GO
/****** Object:  StoredProcedure [dbo].[PROC_LGGetCustomerDataForRequest]    Script Date: 04/16/2014 11:28:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC_LGGetCustomerDataForRequest]

/*Variable*/
@OrderId varchar(10)
AS
declare @DataSource varchar(10),
@PropertyType varchar(10),/*For dbo.PostMover specially*/
@PriceRange int, /*For dbo.premover specially*/
@Radius varchar(10) 
select @PriceRange=PriceRange,@PropertyType=PropertyType,@Radius=radius from dbo.LGOrder where orderid=@OrderId
/*
Summary:根据提供的OrderId,DataSource,PropertyType,Zips,Radius来获取符合指定条件的客户数据。
*/
BEGIN
 
IF @DataSource IS NOT NULL AND @DataSource = 'premover'
BEGIN

SELECT *  INTO dbo.LGPremoverDataCampaignHistory_Backup FROM dbo.LGPremoverDataCampaignHistory 

truncate table dbo.LGPremoverDataCampaignHistory

INSERT INTO dbo.LGPremoverDataCampaignHistory
    ( OrderId ,idkey2,zip ,state,city,address,idkey,addtype,plus4,scrapedt,dpb,price ,
     buy_flag ,    date,AddDate)
SELECT @OrderId AS OrderId,[idkey2],[zip],[state]
    ,[city],[address],[idkey],[addtype],[plus4],[scrapedt]
    ,[dpb],[price],[buy_flag],[date],GETDATE() AS AddDate
    from dbo.premover
        where zip in((SELECT b.zipto
              from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
              ON a.codes=b.zipfrom
              where orderid=@OrderId  AND b.Radius<= @Radius
              AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
              AND b.ZipTo NOT IN(SELECT codes FROM  dbo.LGOrderZips where orderid=@OrderId  )
              GROUP BY ZipTo)
              UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
              AND price <=@PriceRange
END

ELSE IF @DataSource IS NOT NULL AND @DataSource = 'postmover'
BEGIN

SELECT * INTO dbo.LGPostMoveWeeklyDataCampaignHistory_Backup FROM [dbo].[LGPostMoveWeeklyDataCampaignHistory] 

truncate table dbo.LGPostMoveWeeklyDataCampaignHistory

    INSERT INTO [POND].[dbo].[LGPostMoveWeeklyDataCampaignHistory]
           ([OrderId],[fname],[lname],[zip],[plus4],[address],[city],[state],[pub_dt],[seg_nbr]
           ,[fullname],[dpbc],[addtype],[dwell_typ],[AddDate])
           SELECT @OrderId AS OrderId,[fname],[lname],[zip],[plus4],[address],[city],[state],[first_pub_dt],[seg_nbr]
    ,[fullname],[dpbc],[addtype],[dwell_typ],GETDATE() AS AddDate
     from postmover
where zip in((SELECT b.zipto
              from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
              ON a.codes=b.zipfrom
              where orderid=@OrderId  AND b.Radius<= @Radius 
              AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
              AND b.ZipTo NOT IN(SELECT codes FROM  dbo.LGOrderZips where orderid=@OrderId  )
              GROUP BY ZipTo)
              UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
              AND dwell_typ=@PropertyType
END
    
ELSE IF @DataSource IS NOT NULL AND @DataSource = 'both'
BEGIN

        SELECT *  INTO dbo.LGPremoverDataCampaignHistory_Backup FROM dbo.LGPremoverDataCampaignHistory 
        
        truncate table dbo.LGPremoverDataCampaignHistory

        INSERT INTO dbo.LGPremoverDataCampaignHistory
    ( OrderId ,idkey2,zip ,state,city,address,idkey,addtype,plus4,scrapedt,dpb,price ,
     buy_flag ,    date,AddDate)
SELECT @OrderId AS OrderId,[idkey2],[zip],[state]
    ,[city],[address],[idkey],[addtype],[plus4],[scrapedt]
    ,[dpb],[price],[buy_flag],[date],GETDATE() AS AddDate
    from dbo.premover
        where zip in((SELECT b.zipto
              from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
              ON a.codes=b.zipfrom
              where orderid=@OrderId  AND b.Radius<= @Radius
              AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
              AND b.ZipTo NOT IN(SELECT codes FROM  dbo.LGOrderZips where orderid=@OrderId  )
              GROUP BY ZipTo)
              UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
              AND price <=@PriceRange
                    
        SELECT * INTO dbo.LGPostMoveWeeklyDataCampaignHistory_Backup FROM [dbo].[LGPostMoveWeeklyDataCampaignHistory] 

        truncate table dbo.LGPostMoveWeeklyDataCampaignHistory

        INSERT INTO [POND].[dbo].[LGPostMoveWeeklyDataCampaignHistory]
           ([OrderId],[fname],[lname],[zip],[plus4],[address],[city],[state],[pub_dt],[seg_nbr]
           ,[fullname],[dpbc],[addtype],[dwell_typ],[AddDate])
           SELECT @OrderId AS OrderId,[fname],[lname],[zip],[plus4],[address],[city],[state],[first_pub_dt],[seg_nbr]
    ,[fullname],[dpbc],[addtype],[dwell_typ],GETDATE() AS AddDate
     from postmover
where zip in((SELECT b.zipto
              from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
              ON a.codes=b.zipfrom
              where orderid=@OrderId  AND b.Radius<= @Radius 
              AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
              AND b.ZipTo NOT IN(SELECT codes FROM  dbo.LGOrderZips where orderid=@OrderId  )
              GROUP BY ZipTo)
              UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
              AND dwell_typ=@PropertyType
END

END

 

1.若在创建存储过程中，发现某行报错。

如：某行处的END出错。（导致我郁闷了很久！）

这就说明BEGIN与END之间的代码块中有没有检查出的错误，而你需要做的就是找出错误并修改。

2.在存储过程内部定义参数变量之后，如果在代码块中有可能无法为其赋值，则在BEGIN之后为其手动赋初值，否则在该参数变量在存储过程执行完毕后为NULL。

3.如果在存储过程执行完毕后，没有得到预期的数据或值，则对代码块中的关键代码进行手动调试（print，select）；如果手动调试变量没有问题就检查前面的条件性的语句进行检查是否有条件遗漏。

4.数据库端的程序编写可以进行面向对象思想的包装，如果发现某一系列相同的操作过多，则对代码进行重构，封装（例如增加自定义函数等）。

5.在ViewCode1中：truncate table dbo.LGPremoverDataCampaignHistory 将该表进行快速删除。详情：使用 TRUNCATE TABLE 删除所有行

6.在ViewCode2中：SELECT @dateDiffValue= DATEDIFF(WEEK,(SELECT TOP 1 CONVERT(VARCHAR(10),EnteredDate,121) FROM dbo.LGTobeOrder_CCBatch),GETDATE()) ，本例计算是否在本周内。经测试上周日到本周六在同一个星期内，此应为美国周标准，测试说明：若值为1则说明两个日期相差一周且startdate在当前日期enddate的上一周；当值为0则说明两个日期在同一周内；当值为-1则说明两个日期相差一周且startdate在当前日期enddate的下一周。

详情：DATEDIFF (Transact-SQL)

 

个人体会：

1.一定要把数据库的语法掌握熟练，就像做编程一样，只有熟练掌握了基本的语法，再复杂的业务逻辑或技术难题都能一点一点地解决。

2.学习技术可以先应用，但如果想学精并与其他的技术相通，就一定要学习其原理。（本人菜鸟一枚，在此建议各位像我一样的菜鸟）