title: Error in Customized Procedure "Must declare the scalar variable @startdate"
body: <p>I have a procedure to fetch data as per the criteria passed. I works fine with @month, @year, @quater. </p>
  
  <pre><code>ALTER PROCEDURE dbo.SPReportTimeSpan
      (
      @Week int = null,
      @Month int = null,
      @Year int = null,
      @Quater int = null
      )
  AS
      SET NOCOUNT ON 
      DECLARE @sql nvarchar(4000)
  
      If (@Week) IS NOT NULL
      BEGIN
          DECLARE @startdate Date, @enddate Date
          EXEC dbo.SPReturnStartEndDateOfSpecifiedWeek  @Week, @startdate OUTPUT, @enddate OUTPUT 
          SELECT @startdate, @enddate
      END
  
      SELECT @sql='SELECT
      CRMDR.Id as Id,.  
      CRMDR.Request_For_Id as Request_For_Id
  
      From [CRM].[dbo].[CRM_Doctor_Request] AS CRMDR
  
      WHERE CRMDR.Is_Deleted=0 '
      If (@Month) IS NOT NULL
          SELECT @sql=@sql + ' AND MONTH(CRMDR.Date_Created) like (@Month)  '
      If (@Year) IS NOT NULL
          SELECT @sql=@sql + ' AND YEAR(CRMDR.Date_Created) like (@Year) '
      If (@Week) IS NOT NULL
          SELECT @sql=@sql + ' AND (CRMDR.Date_Created) BETWEEN (@startdate) AND (@enddate) '
      If (@Quater) IS NOT NULL
          IF (@Quater = 1)
              SELECT @sql=@sql + ' AND MONTH(CRMDR.Date_Created) in (4,5,6) '
          IF (@Quater = 2)
              SELECT @sql=@sql + ' AND MONTH(CRMDR.Date_Created) in (7,8,9) '
          IF (@Quater = 3)
              SELECT @sql=@sql + ' AND MONTH(CRMDR.Date_Created) in (10,11,12) '
          IF (@Quater = 4)
              SELECT @sql=@sql + ' AND MONTH(CRMDR.Date_Created) in (1,2,3) '
  
  
      SELECT @sql=@sql + ' ORDER BY CRMDR.Id DESC '
  
      EXEC sp_executesql @sql, N'@Month int, @Year int, @Quater int', 
                     @Month, @Year, @Quater
  
      RETURN
  </code></pre>
  
  <p>But for @week it gives error.</p>
  
  <pre><code>Running [dbo].[SPReportTimeSpan] ( @Week = 3, @Month = &lt;NULL&gt;, @Year = &lt;NULL&gt;, @Quater = &lt;NULL&gt; ).
  
  Must declare the scalar variable "@startdate".
  Column1                        Column2                        
  ------------------------------ ------------------------------ 
  2013-02-10 00:00:00.0000000    2013-02-17 00:00:00.0000000    
  No rows affected.
  (1 row(s) returned)
  @RETURN_VALUE = 0
  Finished running [dbo].[SPReportTimeSpan].
  </code></pre>
  
  <p>I am not getting why its not working with week criteria. Please help if anyone have idea.</p>