title: SQL Must declare the scalar variable
body: <p>Below is the procedure.  I keep getting SQL Must declare the scalar variable "@sql" even though i have converted @startdate to varchar.  What am i missing?     </p>
  
  <pre><code>CREATE PROCEDURE [dbo].[project_revenue_forecast_monthly]
  @startDate datetime,
  @sql varchar(max)
  AS
  
  SET @sql = 'SELECT CLIENTNAME, PROJECTNAME, PROJECTSD, 
      isnull([' + REPLACE(SUBSTRING(CONVERT(varchar, @startDate, 13), 4, 8), ' ', '') + '],0) as
        [' + REPLACE(SUBSTRING(CONVERT(varchar, @startDate, 13), 4, 8), ' ', '') + '], 
      isnull([' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 1, @startDate), 13), 4, 8), ' ', '') + '],0) as  
        [' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 1, @startDate), 13), 4, 8), ' ', '') + '],
      isnull([' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 2, @startDate), 13), 4, 8), ' ', '') + '],0) as  
        [' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 2, @startDate), 13), 4, 8), ' ', '') + '],
      isnull([' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 3, @startDate), 13), 4, 8), ' ', '') + '],0) as  
        [' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 3, @startDate), 13), 4, 8), ' ', '') + '],
      isnull([' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 4, @startDate), 13), 4, 8), ' ', '') + '],0) as  
        [' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 4, @startDate), 13), 4, 8), ' ', '') + '],
      isnull([' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 5, @startDate),  13), 4, 8), ' ', '') + '],0) as  
        [' + REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 5, @startDate), 13), 4, 8), ' ', '') + '],
      FORECASTTIMEDAYS, BILLINGRATEDAILY
      FROM
        (
        SELECT
          CLIENTNAME, PROJECTNAME, PROJECTS, SCHENTRYDATE, FORECASTTOTAL
        FROM viewprojscheduling_group
        ) AS SourceTable
      PIVOT
        (
        SUM(FORECASTTOTAL)
        FOR SCHENTRYDATE IN (' +
                QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, @startDate, 13), 4, 8), ' ', '')) + ', ' +
                QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 1, @startDate), 13), 4, 8), ' ', '')) + ', ' +
                QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 2, @startDate), 13), 4, 8), ' ', '')) + ', ' +
                QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 3, @startDate), 13), 4, 8), ' ', '')) + ', ' +
                QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 4, @startDate), 13), 4, 8), ' ', '')) + ', ' +
                QUOTENAME(REPLACE(SUBSTRING(CONVERT(varchar, DATEADD(MONTH, 5, @startDate), 13), 4, 8), ' ', '')) + ')
        ) AS PivotTable'
  
  execute(@sql)
  </code></pre>