sql server 2000 dynamic sql, concatenating with int param 1

[tempo1]

Hi everyone,
I try to form a dynamic-sql command where an "int" param is involved and i dont know how to do it. My code is:

/*EXEC statusecond 'aaa', '20070801'*/
ALTER PROCEDURE statusecond @stat_param VARCHAR(50), @date_param SMALLDATETIME
AS
BEGIN TRANSACTION

DECLARE @year_stat INT
SET @year_stat=YEAR(@date_param)

DECLARE @dynamic VARCHAR(2000)
SET @dynamic=
'
SELECT phone1
FROM
contact1
where
department LIKE ' + 
''''+@stat_param+''''+
'
AND YEAR(ext3)='+
@year_stat

exec(@dynamic)
COMMIT

when i run it i get the following error message:

Server: Msg 245, Level 16, State 1, Procedure statusecond, Line 10
Syntax error converting the varchar value '
SELECT phone1
FROM
contact1
where
department LIKE 'aaa'
AND YEAR(ext3)=' to a column of data type int.

Can anyone help me with that please ?
Thanks

 

[bborissov]

SET @dynamic='SELECT phone1
                     FROM contact1
              where department LIKE ' +''''+@stat_param+''''+'
                    AND YEAR(ext3)='+CAST(@year_stat as varchar(20))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tempo1]

Thanks a lot Borislav !
Glad you're here again to help.

 

[bborissov]

I didn't have time to explain, by when you build a string all variables you use must be converted to string (no mater what function you will use CAST or CONVERT).

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP