Making simple of Stored Procedure code

Question

Could you any one suggest me, what is the alternate approach to write the below stored procedure in a simple way

If you observ in all insert statements WHERE condition is changing
Please see below where conditions

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 )  

Please find the below Stored procedure which i have to make simple

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

Where to start....

First to remove the time from a date use date functions, rather than varchar conversion, although in this case it is not that detrimental it is good practise:

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

Next you select SATwoWeeks.EquipmentCode AND SATwoWeeks.TestTypeCode, from #tempSlotsAvailabityForNextTwoDays SATwoWeeks, yet when you create #tempSlotsAvailabityForNextTwoDays neither of these columns are defined:

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 )

It is difficult to infer what you are trying to do given that I have no idea where EquipmentCode and TestTypeCode come from, however I'd suggest scrapping using temp tables completely, as you are not reusing your data, if you are using them just to make the SQL more legible consider using Common Table Expressions. e.g.

;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

Alternatively, if Table1 is large enough to warrant extracting the data before joining, consider using a single temporary table:

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

Or if you are looking to aggregate your data by day you could use 'PIVOT'

;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

Finally, have you considered records that may be in the NextThreeDays but not in the Nexttwodays, these will never appear since NextTwoDays is the table you are selecting from. You may need to create another table that contains records for all days, or use FULL JOINs

Ultimately I think more information is required to fully answer your question.

Answer 2

You can try something like this:

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

(Note: I can't test it, but the general solution is a PIVOT and only one temp table)