Solved

keyword search in stored procedure

Posted on 2009-05-17
3
225 Views
Last Modified: 2012-05-07
We need a stored procedure that list tall he recordset with a given column containing specified keywords (please not the s:).
For example, MyTable contains the column MyMemo, and we need all the recordsets having 'john' AND 'eat' keywords. Keyword can be passed to the stored procedure thru a string like 'john,eat' with a given separator or thru xml format so we can use some request "from @xmlkeywords.nodes" stuff.
Thanks for help








0
Comment
Question by:javilmer
3 Comments
 
LVL 5

Expert Comment

by:Aanvik
ID: 24406279
Try this.
declare @newString varchar(205)

Set @newString = '%Test%'

-- Insert statements for procedure here

select so.name from sysobjects so join syscomments sc on so.id = sc.id 

where sc.text like @newString

order by name

Open in new window

0
 

Author Comment

by:javilmer
ID: 24406580
I dont' think it will work with more than one keywords...
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24408474
you can do this:

declare @x xml, @newx varchar(1000)
set @x = '<search><val>john</val><val>eat</val></search>'

select * from tablename g
where exists(
select 1
from @x.nodes('/search/val') t(c)
where g.mymemo like '%' + c.value('.', 'varchar(100)')  + '%')
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now