sql server 2008 - sql必须声明标量变量

Question

程序如下:我一直得到SQL必须声明标量变量"@sql"，即使我已经转换@startdate为varchar。我错过了什么?

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)

Answer 1

这样做，注意如果你不想传递@Startdate，你也应该改变它的位置。

CREATE PROCEDURE [dbo].[project_revenue_forecast_monthly]
@startDate datetime,
AS
Declare @sql varchar(max)
.....