sql-server - sp_ExecuteSql : The parameterized query expects the parameter @XX, 未提供

Question

我正在尝试构建一个动态存储过程，以允许我将我的字符串 where 子句作为参数的一部分传递。然而，关注 this article从 MSDN 我仍然无法运行它并出现标题所描述的错误。非常感谢任何帮助。

EXEC    [dbo].[Get_Demand_By_Click_Test]        
        @Year = 2014

错误:

Msg 8178, Level 16, State 1, Line 9
The parameterized query '(@Year int)
SELECT
dbo.vw_Demand_By.*
FROM '
expects the parameter '@Year', which was not supplied.

存储过程:

  ALTER PROCEDURE [dbo].[Get_demand_by_click_test]
     @Year INT
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET nocount ON;

      -- Insert statements for procedure here
      DECLARE @SQL             NVARCHAR(max),
              @ParamDefinition NVARCHAR(max)

      SET @SQL = N'
SELECT       
dbo.vw_Demand_By.*

FROM            
dbo.vw_Demand_By 

WHERE  
Year(EventStartDate) = @Year'

   SET @ParamDefinition = N'@Year int';

      EXECUTE Sp_executesql
        @SQL,
        @ParamDefinition;
  END 

Answer 1

你也必须传递参数:

EXECUTE Sp_executesql @SQL, @ParamDefinition, @Year = @Year;