cleaning up my search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

cleaning up my search

Author

Topic

mleduc
Starting Member

2 Posts

Posted - 2005-08-26 : 13:10:31

Hi,

New poster, I'm a programmer with a couple years of entry level C# coding under my belt.

I've recently had to write a SQL server stored procedure for handling website searchs, lacking any great SQL skills I was hoping to submit the procedure and get any feedback. My biggest concern with it is that it uses temp tables, and would love to do without them becuase being a search it is possible more then one person may try to run this SP at the same time.

The procedure is pretty straight forward you tell it what table to search, what PK to get, what field to search, and give it a comma seperated list of words to search for.

Here it is:
*********************************************************************
CREATE PROCEDURE laSearch

@table varchar(50),
@PK varchar(50),
@des varchar(50),
@wholeSearch varchar(200)

AS

Declare @searchItem varchar(100)
Declare @key varchar(100)
Declare @loop int
Declare @parse int
Declare @cmd nvarchar(600)

CREATE TABLE #results (
resultsID int,
descript varchar(200)
)

CREATE TABLE #fullResults (
resultsID int,
hits int,
descript varchar(200)
)

CREATE TABLE #bigTemp (
resultsID int,
descript varchar(200)
)

set @cmd = 'insert #bigTemp select cast('+ @PK + ' as int) as one, cast(' + @des + ' as varchar(200)) as two from ' + @table
exec sp_executesql @cmd

set @loop = 1
set @parse = charindex(',',@wholeSearch)

If @parse = 0
begin
INSERT INTO #results
(resultsID, descript )
select * from #bigTemp where patindex('%' + @wholeSearch + '%', descript)<> 0
set @loop = 0
End

while @loop > 0
begin
set @parse = charindex(',',@wholeSearch)

If @parse > 0
begin
set @searchItem = substring(@wholeSearch,0,@parse )
set @wholeSearch = substring(@wholeSearch,@parse + 1,(len(@wholeSearch)-@parse))
set @parse = @parse - 1
set @searchItem = left(@searchItem,@parse )
INSERT INTO #results
(resultsID, descript )
select * from #bigTemp where patindex('%' + @searchItem + '%', descript)<> 0
end
Else
set @loop = 0

end

INSERT INTO #results
(resultsID, descript )
select * from #bigTemp where patindex('%' + @wholeSearch + '%', descript)<> 0

INSERT INTO #fullResults
(resultsID, hits)
sELECT resultsID, COUNT(*) as 'Hits'
FROM #results
GROUP BY resultsID

UPDATE A
SET A.descript = B.descript
FROM #fullResults as a, #results as B
WHERE A.resultsID = B.resultsID

select resultsID as ID_Key, hits as Hits, descript as Blurb from #fullResults order by hits desc

drop table #results

drop table #fullResults

drop table #bigTemp

GO

********************************************************************

Thanks for any and all feedback,
Mike

mleduc
Starting Member

2 Posts

Posted - 2005-08-26 : 13:43:16

Found some glaring errors just reading my own post

*********************************************************************
alter PROCEDURE laSearch

@table varchar(50),
@PK varchar(50),
@des varchar(50),
@wholeSearch varchar(200)

AS

/********************
Allows for dynamic searching of table and fields
*********************/

Declare @searchItem varchar(100)
Declare @key varchar(100)
Declare @loop int
Declare @parse int
Declare @cmd nvarchar(600)

CREATE TABLE #results (
resultsID varchar(50),
descript varchar(140)
)

CREATE TABLE #fullResults (
resultsID varchar(50),
hits int,
descript varchar(140)
)
create TABLE #bigTemp (
resultsID varchar(50),
descript varchar(2000)
)

set @cmd = 'insert #bigTemp select cast('+ @PK + ' as varchar(50)) as one, cast(' + @des + ' as varchar(2000)) as two from ' + @table
exec sp_executesql @cmd

set @loop = 1

while @loop > 0
begin
set @parse = charindex(',',@wholeSearch)

If @parse > 0
begin
set @searchItem = substring(@wholeSearch,0,@parse )
set @wholeSearch = substring(@wholeSearch,@parse + 1,(len(@wholeSearch)-@parse))
set @parse = @parse - 1
set @searchItem = left(@searchItem,@parse )
INSERT INTO #results
(resultsID, descript )
select resultsID, cast(descript as varchar(140)) from #bigTemp where patindex('%' + @searchItem + '%', descript)<> 0
end
Else
set @loop = 0

end

INSERT INTO #results
(resultsID, descript )
select resultsID, cast(descript as varchar(140)) from #bigTemp where patindex('%' + @wholeSearch + '%', descript)<> 0

INSERT INTO #fullResults
(resultsID, hits)
sELECT resultsID, COUNT(*) as 'Hits'
FROM #results
GROUP BY resultsID

UPDATE A
SET A.descript = B.descript
FROM #fullResults as a, #results as B
WHERE A.resultsID = B.resultsID

select resultsID as ID_Key, hits as Hits, descript as Blurb from #fullResults order by hits desc

drop table #results

drop table #fullResults

drop table #bigTemp

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources