MSSQL - problem with escape characters

[mad_girl]

the problem is i wanna make this piece of code work and i don know what the problem is , i think it's with the combination of charachters ' and "
Code:

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) "
exec(@query)

[Roelf]

debugging is the easiest if you display the complete query before you execute it.

Can you show us the query which you are sending to the database?

[guelphdad]

Please specify in your thread title/post which database you are using when posting in this section. there are different syntaxes for different databases.

[mad_girl]

I'm using SQL database sorry for not saying that ,thought it's the default..
Roelf :
here's the code of my procedure if that what u asked for

alter proc findUsers
@id varchar(10),@name varchar(15),@ref varchar(10),@age varchar(2),@option varchar(3)
as
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)"
exec(@query)
go

[guelphdad]

Originally Posted by mad_girl

I'm using SQL database sorry for not saying that ,thought it's the default..

no, no default here. our main forum is for mysql questions. this one is a generic one for other database questions as we don't get that many. thanks for clarifying.

[Roelf]

alter proc findUsers
@id varchar(10),@name varchar(15),@ref varchar(10),@age varchar(2),@option varchar(3)
as
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)'
exec(@query)
go

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?

print(query)
exec(query)

And post what it looks like?