sp_ExecuteSql: The parameterized query expects the parameter @XX, which was not supplied

Question

I'm trying to build a dynamic stored procedure to allow me to pass my string where clause as part of the parameter. However, following this article from MSDN I still couldn't get it running and having the error as described by the title. Any help is much appreciated.

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

Error:

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.

Stored procedure:

  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

You need to give a value to the @Year parameter. To execute the code use:

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

UPDATE In response to your comments you can assign any variable in the place of the fixed 2014 value. In your code it will look like :

  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,
        @Year = @Year
  END