Hi All,
I'm a newbie in stored procedure programming. I wanted to learn how to perform keyword(s) search with stored procedures. Take the Pubs database for example. How can i create a stored procedure which takes in a string of keywords and return the title_id, title and notes column ? like if i pass in "computer easy" as keywords then the stored procedure should return all the rows with ANY of these keywords in those 3 columns. Can anyone give me some ideas on how to do this? like do i have to use dynamic sql?, any tutorials or sample codes? Thanks in advance!
regards
|||Hi ndinakar,
thanks for replying, do you have the links to where i can donwload those books that u mentioned thanks?
regards|||you can search on microsoft website ( or even google ?) . Thats what I would have done too.|||Hi ndinakar,
can you please go to my other posthttp://forums.asp.net/986519/ShowPost.aspx and see if u could answer my questions there as well? because i've posted for quite a while but still have not got a reply yet.
regards|||
hi ndinakar, i'm just trying out a really simple stored procedure but still couldn't get it to work dunno why? can you please help me thanks
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'test_sp' AND type = 'P')
DROP PROCEDURE test_sp
GO
USE pubs
GO
CREATE PROCEDURE test_sp
AS
create table #temp(string1 varchar(4000), string2 varchar(4000))
insert into #temp (string1, string2) values('string1','string2')
GO
The error message that i got was
Invalid column name 'string1'.
kakusei wrote:
CREATE PROCEDURE test_sp
AS
create table #temp(string1 varchar(4000), string2 varchar(4000))
insert into #temp (string1, string2) values('string1','string2')GO
Try this
You are creating a temporary table which might not exists when you query it. You table creation is correct. I tested it.
Just add a select command after the insert: select * from #temp
Thanks
If you are not building any kind of index then you can use the like operator as shown below:
select * from <table_name> where title like '%computer easy%' or notes like '%computer easy%'
You can make pass the search string to a sp and then build the dynamic sql inside the sp. finally use the exec command to execute the dynamic sql:
Here is the sp:
create proc sp_TextSearch
@.sTextToSearch varchar(50)
as
declare @.sDynamicSQL varchar(400)
set @.sDynamicSQL = 'select * from <table_name> where title like''%'+@.sTextToSearch +'%'' or notes like ''%'+ @.sTextToSearch +'%'''
--print @.sDynamicSQL
exec(@.sDynamicSQL)
Hi ndinakar,
It worked for you ? but how come it doesn't work for me?? Well what i want to do is to create a stored procedure which will take in a keyword string as parameter then break up the string and store the keywords into the #temp table then after that with a while loop to loop through the temp table and dynamically build a sql statement to perform the search (dunno if that is possible yet). So now i'm just testing out how to create the #temp table first, but i couldn't even get that to work :(
i just got this error message
(1 row(s) affected)
Server: Msg 207, Level 16, State 1, Procedure test_sp, Line 5
Invalid column name 'string1'.
|||
Hi musa,
Thanks for your reply and your sample code. But what i want to do ultimately is not just to return the records which only contain the keyword "computer easy" but to return the records which contains the keyword "computer" and "easy" and "computer easy" if i pass in the string "computer easy". So now i'm just thinking of a way to break up the keywords first and then make a loop to loop throught those keywords to build the search query dynamically. I'm think of breaking up the keyword string and storing it into a #temp table first. Dunno if i'm on the right track or not!? so that's why i'm trying to create the #temp table
but couldn't even get that to work
Ok now when i renamed the #temp table to #temp1
but then i put a select * from #temp1 after the insert it didn't show anything
It should show the string1 and string2 right??
forget about my last post about it didn't show anything. i'm stupid forgot to run the procedure!
So now i will have to try and pass in a keyword parameter and break that up and store it into the temp table so that if i pass in "computer easy"
it will store it in the temp table like
keyword id | keyword
--------
1 | computer
2 | easy
any suggestions on how to do that?|||
Here's a function that will take a delimited string and split it and return the values in a table.
--------
CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END
RETURN
END
-----------------
After you create the function you can use it as :
Declare @.list varchar(200)
set @.list = 'computer,easy'
Select * from YourTable
where keywordcolumn in (Select Element from dbo.Split(@.list, ','))
Hi ndinakar,
can you please see what's wrong with my code below thanks, i can't get it to work. And thanks for your code, but i wanted to do something like the full-text search Contains clause. So i think the code would not work for me
set @.list = 'computer,easy'
Select * from YourTable
where keywordcolumn in (Select Element from dbo.Split(@.list, ','))
--------------------
MY CODE
-----------------------
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'keyword_search' AND type = 'P')
DROP PROCEDURE keyword_search
GO
USE pubs
GO
CREATE PROCEDURE keyword_search
@.keywords varchar(200)
AS
CREATE TABLE #keyword_table
(keyword_index tinyint IDENTITY(1,1) NOT NULL PRIMARY KEY, keyword varchar(200), key_param varchar(5))
DECLARE @.sql nvarchar(1000)
DECLARE @.keyword_count tinyint
DECLARE @.paramlist nvarchar(1000)
declare @.1 varchar(200)
declare @.2 varchar(200)
declare @.3 varchar(200)
declare @.4 varchar(200)
declare @.5 varchar(200)
declare @.6 varchar(200)
declare @.7 varchar(200)
declare @.8 varchar(200)
declare @.9 varchar(200)
declare @.10 varchar(200)
BEGIN
DECLARE @.delimiter char(1)
DECLARE @.temp_keywords varchar(200)
DECLARE @.keyword_length smallint
DECLARE @.id varchar(5)
SET @.delimiter = ','
SET @.temp_keywords = @.keywords
SET @.keyword_count = 0
WHILE CHARINDEX(@.delimiter, @.temp_keywords) > 0
BEGIN
SELECT @.keyword_length = CHARINDEX(@.delimiter, @.temp_keywords)
INSERT INTO #keyword_table (keyword) values (SUBSTRING(@.temp_keywords,0,@.keyword_length))
SELECT @.temp_keywords = SUBSTRING(@.temp_keywords, @.keyword_length + 1, LEN(@.temp_keywords))
select @.keyword_count = @.keyword_count + 1
END
INSERT INTO #keyword_table (keyword) values (SUBSTRING(@.temp_keywords,0,@.keyword_length))
select @.keyword_count = @.keyword_count + 1
select @.sql = 'select title, notes from pubs..titles '
IF @.keyword_count = 3
BEGIN
select @.sql = @.sql + 'where title like ' + quotename('%' + @.1 + '%','''')
+ ' or notes like ' + quotename('%' + @.1 + '%','''') + ' and '
select @.sql = @.sql + 'title like ' + quotename('%' + @.2 + '%','''')
+ ' or notes like ' + quotename('%' + @.2 + '%','''') + ' and '
select @.sql = @.sql + 'title like ' + quotename('%' + @.3 + '%','''')
+ ' or notes like ' + quotename('%' + @.3 + '%','''')
-- select @.sql = @.sql + 'where title like''%@.1%'' or notes like''%@.1%'' and '
-- select @.sql = @.sql + 'title like''%@.2%'' or notes like''%@.2%'' and '
-- select @.sql = @.sql + 'title like''%@.3%'' or notes like''%@.3%'''
END
select @.paramlist ='@.1 varchar(200),
@.2 varchar(200),
@.3 varchar(200)'
DECLARE keyword_cursor CURSOR
FOR SELECT keyword FROM #keyword_table
OPEN keyword_cursor
FETCH NEXT FROM keyword_cursor into @.1
FETCH NEXT FROM keyword_cursor into @.2
FETCH NEXT FROM keyword_cursor into @.3
END
exec sp_executesql @.sql, @.paramlist, @.1, @.2, @.3
GO
EXEC keyword_search 'computer,easy,user'
No comments:
Post a Comment