Problem with passing variable parameters to an SP

[suel]

I have written the following SP. This can have have aany or all of the 3 parameters.
In Enterprise Manager, on checking the syntax of the SP it says it is OK. When I try and test this in QA I get the following message :
'Syntax error converting varchar value 'and hospcode =' to a column of data type int'

HELP .... Please ...
Thanks
Suel

CREATE PROCEDURE slFind_Errors_Rpt

	@hcode	int = null,
	@survyear	int = null,
	@speriod	int = null
as

declare @select varchar(255)
declare @from varchar(255)
declare @where varchar(255)
	

select @select = 'select serialnum,hospcode,errorid,syear,survperiod '
select @from = 'from errorlog'
select @where = ''


if @hcode is not null
	begin
	select @where  = @where + ' and  hospcode =  ''' + @hcode + ''''
	end

if @survyear  is not null
	begin
	select @where = @where + ' and syear = ''' + @survyear + ''''
	end

if @speriod is not null
	begin
	select @where = @where + ' and survperiod = ''' + @speriod + ''''
	end

if datalength(@where) <> 0
begin
	select @where = stuff(@where,1,4,'where')
end

print @select + @from + @where

--exec(@select + @from + @where)
GO

 

[mwolf00]

You need to CAST or CONVERT you INT variable to VARCHAR to put them into a string....

if @hcode is not null
begin
select @where = @where + ' and hospcode = ''' + CAST(@hcode as varchar(10))+ ''''
end

 

[mwolf00]

BTW - I'm guessing that hospcode is stored as CHAR or VARCHAR and that is why you are putting quotes around your sql string. If they are int then it should read:

if @hcode is not null
begin
select @where = @where + ' and hospcode = ' + CAST(@hcode as varchar(10))
end

 

[suel]

Cheers mwolf00 using CAST did the trick !

Many Thanks

Suel