PDA

View Full Version : MSSQL - problem with escape characters



mad_girl
Aug 27th, 2007, 06: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 @[email protected]+ "cast(ID as varchar) like'%'+ltrim(@id)+'%' "
set @[email protected]+ @option+" userName like'%'+ltrim(@name)+'%' "
set @[email protected]+ @option+" cast(referrer as varchar) like'%'+ltrim(@ref)+'%' "
set @[email protected]+ @option+" age=cast(@age as int) "
exec(@query)

Roelf
Aug 27th, 2007, 07: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
Aug 27th, 2007, 01: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
Aug 27th, 2007, 08: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 @[email protected]+ "cast(ID as varchar) like '%'+ltrim(@id)+'%' "
set @[email protected]+ @option+" userName like '%'+ltrim(@name)+'%' "
set @[email protected]query+ @option+" cast(referrer as varchar) like '%'+ltrim(@ref)+'%' "
set @[email protected]+ @option+" age=cast(@age as int)"
exec(@query)
go

guelphdad
Aug 28th, 2007, 01: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
Aug 29th, 2007, 06: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 @[email protected]+ 'cast(ID as varchar) like ''%'+ltrim(@id)+'%'' '
set @[email protected]+ @option+' userName like ''%'+ltrim(@name)+'%'' '
set @[email protected]+ @option+' cast(referrer as varchar) like ''%'+ltrim(@ref)+'%'' '
set @[email protected]+ @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?