alter proc findUsers
@id varchar(10),@name varchar(15),@ref varchar(10),@age varchar(2),@option varchar(3)
declare @query char(80)
set @query='select * from users where '
set @query=@query+ 'cast(ID as varchar) like ''%'+ltrim(@id)+'%'' '
set @query=@query+ @option+' userName like ''%'+ltrim(@name)+'%'' '
set @query=@query+ @option+' cast(referrer as varchar) like ''%'+ltrim(@ref)+'%'' '
set @query=@query+ @option+' age=cast(@age as int)'
when you are building a string which eventually will become a query with text delimiters (quotes), you have to double the quotes when building the string. So the double quotes are in fact two single quotes!
Personally, i dont like building a string and execute that, but that is a matter of taste i think. What is that option parameter doing so many times in the query?
Can you print out the query before you execute it?
And post what it looks like?