SQL Search with params and LIKE

[LoveToCode]

Hello,

Why doesn't this work as expected?

select * from user   

where   
  
username like '%@name%'

I can tell its something stupid - I dont think I'm using the param and like correctly together.

Thanks,

-- jenni

 

[philhege]

declare @v_sql varchar(256)
set @v_sql = 'select * from user

where

username like ''%'+@name+'%'''

EXEC(@v_SQL)



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[gmmastros]

You don't need dynamic SQL to do this.

select * 
from   user   
where  username like [!]'%' + @name + '%'[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[philhege]

Thanks, George; it was a brain cramp.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[LoveToCode]

Hey thanks alot that worked great! When I added the filter to the SQL query as explained above it returns accurate results. After I added a bunch more parameters though it returns all the rows in that (the user) table...

ALTER procedure sp_search_people  
  
@username varchar(250),
@fname varchar(250),
@lname varchar(250),   
@email varchar(250),   
@state varchar(250),   
@city varchar(250),
@online varchar(20),   
@hasImage varchar(20),   
@sex varchar(20)  
  
as  

select * from user where  

username like '%' + @username + '%'
or fname like '%' + @fname + '%' 
or lname like '%' + @lname + '%'
or state like '%' + @state + '%' 
or city like '%' + @city + '%'
or isonline like '%' + @online + '%'
or hasMainImage like '%' + @hasImage + '%'
or sex like '%' + @sex + '%'

I suspect that its because I'm using the "OR" operator when I should be using some other method...

Thanks!

 

[ptheriault]

it depends on what you want returned in your result set. Something else to consider, you are going to get very poor performance from that query. For two reasson.
First you are using 'SELECT *' second, in your where clause you have % + variable + %. If it is possible you should change it to variable + %.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[LoveToCode]

I want to see results only if there is a value in the variables, if there is nothing to search for, then I dont want to evaluate that parameter.

This is for a web search form. Imagine using a form to find only someone by their first name. So if I search for someone with the first name of "Jenni" I want to see only columns where rows have the value of "Jenni" (or "~-=Jenni=-~", thus my "%+Var+%" )

When I am done I will convert this from a fire hose select and list just the columns I want. This is my first search page so I want to fool around and see how I can get it to best work correctly and efficiently. Please let me know how I can limit the result set.

thank you!

-- Jenni

 

[ptheriault]

Jenni,
You're going to want your where clause to look something like this. I don't think you want the wild card infront of all your variable. That would give you a result like this...

Say the user whishes to search for fnames that start with N. If you have the wild card in the front and the back you are going to return any fname that has a 'N' in it.
Like
Nancy
Jenni

Is that what you want?

WHERE (@username is null or username like  @username + '%')
and (@fname is null or fname like @fname + '%')
etc...

- Paul
- If at first you don't succeed, find out if the loser gets anything.