简化存储过程代码

Question

你能不能建议我，用一种简单的方式编写下面的存储过程的替代方法是什么？

如果您在所有的插入语句中观察到条件正在改变

请参阅下面的条件

WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,1,@lCurrentDateTime )  
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,2,@lCurrentDateTime )  
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,3,@lCurrentDateTime )  
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,4,@lCurrentDateTime )  
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,5,@lCurrentDateTime )  
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,6,@lCurrentDateTime )  
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,7,@lCurrentDateTime )  

请找到下面的存储过程，我必须制作简单的

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
END MAINTENANCE WRAPPER.
===============================================================================*/
CREATE   PROCEDURE [dbo].[FamilyDates]
(
    @pErrorMessage      VARCHAR(500) = NULL OUT      
)
AS   
BEGIN   

    SET NOCOUNT ON  

    BEGIN TRY

    DECLARE @lCurrentDateTime DATETIME  
    SET @lCurrentDateTime = CONVERT(VARCHAR(10), GETDATE(), 101)


            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextTwoDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextTwoDays'
            INTO    #tempSlotsAvailabityForNextTwoDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,1,@lCurrentDateTime )

            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextThreeDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextThreeDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays  'OpenSlotsForNextThreeDays' 
            INTO    #tempSlotsAvailabityForNextThreeDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,2,@lCurrentDateTime )

            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextFourDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextFourDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays  'OpenSlotsForNextFourDays'
            INTO    #tempSlotsAvailabityForNextFourDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,3,@lCurrentDateTime )

            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextFiveDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextFiveDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextFiveDays'
            INTO    #tempSlotsAvailabityForNextFiveDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,4,@lCurrentDateTime )

            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextSixDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextSixDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextSixDays'
            INTO    #tempSlotsAvailabityForNextSixDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,5,@lCurrentDateTime )

            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextSevenDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextSevenDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextSevenDays'
            INTO    #tempSlotsAvailabityForNextSevenDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,6,@lCurrentDateTime )

            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForEightDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForEightDays
            SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextEightDays'
            INTO    #tempSlotsAvailabityForEightDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,7,@lCurrentDateTime )


            SELECT  SATwoWeeks.EquipmentCode, SATwoWeeks.TestTypeCode, 
                    ISNULL(SAThreeDays.OpenSlotsForNextThreeDays, '0') 'OpenSlots ForNextTwoDays',
                    ISNULL(SAFourDays.OpenSlotsForNextFourDays, '0') 'OpenSlots ForNextThreeDays',
                    ISNULL(SAFiveDays.OpenSlotsForNextFiveDays,'0') 'OpenSlots ForNextFourDays',
                    ISNULL(SASixDays.OpenSlotsForNextSixDays,'0') 'OpenSlots ForNextFiveDays',
                    ISNULL(SASevenDays.OpenSlotsForNextSevenDays, '0') 'OpenSlots ForNextSixDays',
                    ISNULL(SAEightDays.OpenSlotsForNextEightDays,'0') 'OpenSlots ForNextSevenDays',
                    ISNULL(SANineDays.OpenSlotsForNextEightDays,'0') 'OpenSlots ForNextEightDays'
            FROM    #tempSlotsAvailabityForNextTwoDays SATwoWeeks 
                    LEFT    JOIN #tempSlotsAvailabityForNextThreeDays SAThreeDays ON (SAThreeDays.ClientId = SATwoWeeks.ClientId )
                    LEFT    JOIN #tempSlotsAvailabityForNextFourDays SAFourDays ON (SAFourDays.ClientId = SATwoWeeks.ClientId  )
                    LEFT    JOIN #tempSlotsAvailabityForNextFiveDays SAFiveDays ON (SAFiveDays.ClientId = SATwoWeeks.ClientId )
                    LEFT    JOIN #tempSlotsAvailabityForNextSixDays SASixDays ON (SASixDays.ClientId = SATwoWeeks.ClientId )
                    LEFT    JOIN #tempSlotsAvailabityForNextSevenDays SASevenDays ON (SASevenDays.ClientId = SATwoWeeks.ClientId )
                    LEFT    JOIN #tempSlotsAvailabityForEightDays SAEightDays ON (SAEightDays.ClientId = SATwoWeeks.ClientId )


        END TRY 
        BEGIN CATCH 
            SET @pErrorMessage = CONVERT(VARCHAR(10),ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
        END CATCH
        END 

GO

Answer 1

你可以试试这样的东西：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
END MAINTENANCE WRAPPER.
===============================================================================*/
CREATE   PROCEDURE [dbo].[FamilyDates]
(
    @pErrorMessage      VARCHAR(500) = NULL OUT      
)
AS   
BEGIN   

    SET NOCOUNT ON  

    BEGIN TRY

    DECLARE @lCurrentDateTime DATETIME  
    SET @lCurrentDateTime = CONVERT(VARCHAR(10), GETDATE(), 101)


            IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL  
            DROP TABLE #tempSlotsAvailabityForNextDays
            SELECT  ClientId,ClientName, OpenSlotsForNextDays, 
DATEDIFF(day, @lCurrentDateTime, ChamberAvailaBilityDate) AS DayNumber
            INTO    #tempSlotsAvailabityForNextTwoDays
            FROM    Table1  TCS
            WHERE   TCS.ChamberAvailaBilityDate  BETWEEN
DATEADD(DAY,1,@lCurrentDateTime ) 
AND DATEADD(DAY,8,@lCurrentDateTime )



            SELECT  DayNumber,
                    [1], [2], [3], [4], [5], [6], [7]
            FROM (
            SELECT  DayNumber,   OpenSlotsForNextDays     
            FROM    #tempSlotsAvailabityForNextDays SANextDays) AS SourceTable
            PIVOT
            (
             MIN(OpenSlotsForNextDays)
             FOR DayNumber IN ([1], [2], [3], [4], [5], [6], [7])
             ) AS PivotTable


        END TRY 
        BEGIN CATCH 
            SET @pErrorMessage = CONVERT(VARCHAR(10),ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
        END CATCH
        END 

GO

(注意:我不能测试它，但一般的解决方案是一个支点，只有一个临时表)

Answer 2

从哪里开始..。

首先，从日期使用日期函数中删除时间，而不是varchar转换，尽管在这种情况下，这并不是有害的，而是很好的实践：

DECLARE @lCurrentDateTime DATE = CAST(GETDATE() AS DATE)

接下来，从SATwoWeeks.EquipmentCode和SATwoWeeks.TestTypeCode中选择#tempSlotsAvailabityForNextTwoDays SATwoWeeks，但是当您创建#tempSlotsAvailabityForNextTwoDays时，这两个列都没有定义：

IF OBJECT_ID('tempdb..#tempSlotsAvailabityForNextTwoDays') IS NOT NULL  
DROP TABLE #tempSlotsAvailabityForNextTwoDays
SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays 'OpenSlotsForNextTwoDays'
INTO    #tempSlotsAvailabityForNextTwoDays
FROM    Table1  TCS
WHERE   TCS.ChamberAvailaBilityDate  = DATEADD(DAY,1,@lCurrentDateTime )

考虑到我不知道EquipmentCode和TestTypeCode是从哪里来的，所以很难推断出您想要做什么，但是我建议完全放弃使用临时表，因为您不是在重用数据，如果您使用这些数据只是为了使使用公共表表达式更容易读懂。例如：

;WITH NextTwoDays AS
(   SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays AS OpenSlots
    FROM    Table1  TCS
    WHERE   TCS.ChamberAvailaBilityDate = DATEADD(DAY, 1, @lCurrentDateTime)    
), NextThreeDays AS
(   SELECT  ClientId,ClientName, OpenSlotsForNextTwoDays AS OpenSlots
    FROM    Table1  TCS
    WHERE   TCS.ChamberAvailaBilityDate = DATEADD(DAY, 2, @lCurrentDateTime)
), NextFourDays AS
(   ...
)
SELECT  NextTwoDays.ClientID, 
        NextTwoDays.OpenSlots AS NextTwoDays,
        COALESCE(NextThreeDays.OpenSlots, 0) AS NextThreeDays,
        COALESCE(NextFourDays.OpenSlots, 0) AS NextFourDays
FROM    NextTwoDays
        LEFT JOIN NextThreeDays
            ON NextTwoDays.ClientID = NextThreeDays.ClientID
        LEFT JOIN NextFourDays
            ON NextTwoDays.ClientID = NextFourDays.ClientID

或者，如果Table1足够大，需要在加入之前提取数据，那么可以考虑使用一个临时表：

CREATE TABLE #Temp 
(       DayNumber       INT NOT NULL,
        ClientID        INT NOT NULL,
        ClientName      VARCHAR(255) NOT NULL,
        OpenSlots       INT NOT NULL
)
INSERT INTO #Temp
SELECT  DATEDIFF(DAY, @lCurrentDateTime, TCS.ChamberAvailaBilityDate),
        ClientID,
        ClientName,
        OpenSlotsForNextTwoDays
FROM    Table1
WHERE   TCS.ChamberAvailaBilityDate BETWEEN DATEADD(DAY, 1, @lCurrentDateTime) AND DATEADD(DAY, 7, @lCurrentDateTime) 

SELECT  two.ClientID, 
        two.OpenSlots AS NextTwoDays,
        COALESCE(three.OpenSlots, 0) AS NextThreeDays,
        COALESCE(four.OpenSlots, 0) AS NextFourDays
FROM    #Temp two
        LEFT JOIN #Temp three
            ON two.ClientID = three.ClientID
            AND three.DayNumber = 2
        LEFT JOIN #Temp four
            ON two.ClientID = four.ClientID
            AND four.DayNumber = 2
WHERE   two.dayNumber = 1

或者，如果您希望在白天汇总数据，则可以使用“枢轴”。

;WITH Data AS
(   SELECT  DATEDIFF(DAY, @lCurrentDateTime, TCS.ChamberAvailaBilityDate) AS DayNum,
            ClientID,
            ClientName,
            OpenSlotsForNextTwoDays
    FROM    Table1
    WHERE   TCS.ChamberAvailaBilityDate BETWEEN DATEADD(DAY, 1, @lCurrentDateTime) AND DATEADD(DAY, 7, @lCurrentDateTime) 
)
SELECT  ClientID,
        [1] AS NextTwoDays,
        [2] AS NextThreeDays,
        [3] AS NextFourDays,
        [4] AS NextFiveDays,
        [5] AS NextSixDays,
        [6] AS NextSevenDays,
        [7] AS NextEightDays
FROM    Data
        PIVOT
        (   SUM(OpenSlots)
            FOR DayNum IN ([1], [2], [3], [4], [5], [6], [7])
        ) pvt

最后，您是否考虑过可能在NextThreeDays中而不是在Nexttwodays中的记录，这些记录将永远不会出现，因为NextTwoDays是您要从中选择的表。您可能需要创建另一个包含所有天记录的表，或者使用FULL JOIN的

最终，我认为需要更多的信息才能完全回答你的问题。