Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'

Question

_{Wednesday, March 24, 2010 9:16 AM}

CREATE PROCEDURE GetJobs

@JobCategoryID INT = NULL,

@Month VARCHAR(2),

@Year VARCHAR(4),

@PlannedCount INT OUTPUT

AS

BEGIN

DECLARE @Query VARCHAR(MAX)

SET @Query ='SELECT @PlannedCount = COUNT(1) FROM tblWorks 

WHERE MONTH(PlannedDate) = ' + @Month + ' AND 

YEAR(PlannedDate) = ' + @Year

IF (@JobCategoryID IS NOT NULL)

SET @Query += ' AND .JobCategoryID = ' + CONVERT(varchar(4), @JobCategoryID)

EXEC SP_EXECUTESQL  @Query, @PlannedCount output

END

CREATE PROCEDURE MainSP

AS

BEGIN

          DECLARE @PlannedCount INT

          EXEC GetJobs NULL, 8, 2010,  @PlannedCount  OUTPUT

END

While executing MainSP I am getting the following error

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Kindly tell me a solution

Thanks & Regards,

Katte

All replies (2)

_{Wednesday, March 24, 2010 9:17 AM ✅Answered}

DECLARE @Query NVARCHAR(MAX)

declare the variable as NVARCHAR.
KH Tan

_{Wednesday, March 24, 2010 9:23 AM}

Thanks KH Tan.

It works now.