Having some Dynamic SQL issues with INT type

Question

Hello Im using SQL2000 so I build a Dynamic Query and in the last case I have this :

 IF (@apepac is not null and @nompac is not null and @month is not null )
    SELECT @DynaSQL_1= @DynaSQL_1 + ' AND PACIENTE.apellidos like ''' + @apepac + '%'''+
               ' AND PACIENTE.nombres like ''' + @nompac + '%'''+
               ' AND DATENAME(MONTH,honorariotecnologo.fechaestudio) = ''' + @month +'''' +
               ' AND YEAR(honorariotecnologo.fechaestudio) = '+@year+''

so the parameter @year is declared in this way :

DECLARE @year int,

and the error I get from SQL output is :

Msg 245, Level 16, State 1, Line syntax 
43Error to convert the nvarchar value '

What could be wrong?

Thanks!

By the way, Why if the parameter is declared as INT, on the body query it must have to be casted / converted? ...

Answer 1

You have to cast or convert the INT to a NVARCHAR. Google CAST CONVERT TSQL.

Answer 2

You need to cast your @Year as a character value.

Try this:

' AND YEAR(honorariotecnologo.fechaestudio) = ' + CAST(@year AS varchar(10))

Answer 3

You want this to take care of the conversion error...

' AND YEAR(honorariotecnologo.fechaestudio) = '+CAST(@year AS VARCHAR)

You want this if you want to add the single quote to the end of your string.

' AND YEAR(honorariotecnologo.fechaestudio) = '+CAST(@year AS VARCHAR) + ''''