Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

search values in a temp table with like operator Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 8:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
Hi,

I have a SQL proc with a search-parameter (given as coma separated values) which should return all data of a table in which one of the search criteria is valid to any field of the table. At the moment I compare with '=' but I have to change this into like operator. No idea how I can handle this. Help would be highly appreciated!
Thanks!!!

Sue


create table tblclient
( ID int identity(1,1) primary key,
Firstname varchar(50),
Lastname varchar(50),
birthdate smalldatetime
)
go

insert into tblclient
values('John','Singer','01.04.1980'),
('Mary','Smith','21.06.1975'),
('Marylou','Singersmith','11.03.1987'),
('Carl','Smith','11.03.1987')
go

create proc pSearch
@searchparam varchar(400) -- values will be entered like this - seperated by comma: John, Mary, 23.10.1980
as

set @searchparam = @searchparam + ','

declare @value varchar(100)
set @value = ''

-- create temp table to enter search values separated in rows
create table #search(value varchar(100))

-- insert each value into #search
while charindex(',',@searchparam)> 0
begin
set @value = left(@searchparam, charindex(',',@searchparam)-1)

set @searchparam = ltrim(right(@searchparam,len(@searchparam) - charindex(',',@searchparam)))
insert into #search values(@value)

end

-- return all clients which referes to either one of the search criterias with like operator
select id, firstname, lastname, birthdate
from tblclient
where firstname in(select value from #search)
or lastname in(select value from #search)
or convert(varchar(15),birthdate,104) in(select value from #search)
go

exec pSearch 'Mary' -- should return mary and marylou
exec pSearch 'Mary, Smit' -- should return mary, marylou and carl
exec pSearch 'Mary, 11.03.1987' -- should return mary,marylou and carl






Susanne
Post #1476054
Posted Monday, July 22, 2013 9:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
You should really try the 8K Splitter. However, your code won't perform great because SQL Server might not generate the best plan for your query.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476068
Posted Monday, July 22, 2013 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
In addition to what Luis posted you need to check out this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

It explains how to avoid some major performance issues with search type queries.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476076
Posted Monday, July 22, 2013 11:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
Thanks Luis! I have to practice and read carefully as CTE is rather confusing for me ;)... but you think this will solve the problem?

Performance is not the point as in the client table will not be more than 1000 rows.

Thanks!
Sue


Susanne
Post #1476150
Posted Monday, July 22, 2013 11:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
Thanks Sean! But I cannot see how dynamic SQL will help here? The problem is that all fields have to be checked for the search criteria so I cannot put together a dynamic SQL clause. Maybe you can clarify?
Thank you!


Susanne
Post #1476151
Posted Monday, July 22, 2013 11:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,098, Visits: 3,155
Something like this should be very close at least, I think:



create proc pSearch
@searchparam varchar(400) -- values will be entered like this - seperated by comma: John, Mary, 23.10.1980
as

set @searchparam = @searchparam + ','

declare @value varchar(100)
set @value = ''

-- create temp table to enter search values separated in rows
create table #search(value varchar(100) primary key)

-- insert each unique value into #search
while charindex(',',@searchparam)> 0
begin
set @value = left(@searchparam, charindex(',',@searchparam)-1)
set @searchparam = ltrim(right(@searchparam,len(@searchparam) - charindex(',',@searchparam)))
insert into #search select @value where not exists ( select 1 from #search where value = @value )
end
UPDATE STATISTICS #search WITH FULLSCAN

-- return all clients which match any one of the search criteria with like operator
select distinct c.id, c.firstname, c.lastname, c.birthdate
from dbo.tblclient c
inner join #search s on
c.Firstname like s.value
or c.Lastname like s.value
or c.birthdate like s.value

go




SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1476157
Posted Monday, July 22, 2013 12:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?


Susanne
Post #1476178
Posted Monday, July 22, 2013 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
Sue-651097 (7/22/2013)
But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?


Yes you can still do a wildcard search.

inner join #search s on
c.Firstname like s.value + '%'
or c.Lastname like s.value + '%'
or c.birthdate like s.value + '%'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476185
Posted Monday, July 22, 2013 1:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,098, Visits: 3,155
Sean Lange (7/22/2013)
Sue-651097 (7/22/2013)
But this doesn't solve the problem with the like operator as I cannot implement the wildcard %, or?


Yes you can still do a wildcard search.

inner join #search s on
c.Firstname like s.value + '%'
or c.Lastname like s.value + '%'
or c.birthdate like s.value + '%'




Yes, sorry, typo, I left off the " + '%' ".


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1476231
Posted Monday, July 22, 2013 2:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 22, 2013 4:06 AM
Points: 54, Visits: 64
Great!! This works out! So if I cannot find out how CTE will work, I can do it with this solution!
Thanks to all!!


Susanne
Post #1476250
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse