PDA

View Full Version : MSSQL - problem with escape characters



mad_girl
08-27-2007, 07:55 AM
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
08-27-2007, 08:13 AM
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
08-27-2007, 02:02 PM
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
08-27-2007, 09:36 PM
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
08-28-2007, 02:21 PM
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
08-29-2007, 07:17 AM
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?