title: Making simple of Stored Procedure code
body: <p>Could you any one suggest me, what is the alternate approach to write the below stored procedure in a simple way</p>
  
  <p>If you observ in all insert statements WHERE condition is changing<br>
  <strong>Please see below where conditions</strong>  </p>
  
  <pre><code>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 )  
  </code></pre>
  
  <p><strong>Please find the below Stored procedure which i have to make simple</strong>  </p>
  
  <pre><code>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
  </code></pre>