Solved

keyword search in stored procedure

Posted on 2009-05-17
3
236 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 63
SQL Agent Timeout 5 68
SQL query with cast 38 55
convert null in sql server 12 48
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 I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

820 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