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: Today @ 3:37 PM
Points: 3,374, Visits: 7,300
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
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: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
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