mad_girl
08-27-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 @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, 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
08-27-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
08-27-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 @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, 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
08-29-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 @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?
