CODEWITHSUNDEEP
sql-servert-sqlparameterized-query
user3840039
user3840039

Reputation: 47

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

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

Upvotes: 3

Views: 4265

Answers (2)

Andomar
Andomar

Reputation: 238078

You have to pass the parameter as well:

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

Upvotes: 1

Dimt
Dimt

Reputation: 2328

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

Upvotes: 0

Related Questions