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.
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)ASDeclare @searchItem varchar(100)Declare @key varchar(100)Declare @loop intDeclare @parse intDeclare @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 ' + @tableexec sp_executesql @cmdset @loop = 1set @parse = charindex(',',@wholeSearch)If @parse = 0 begin INSERT INTO #results (resultsID, descript ) select * from #bigTemp where patindex('%' + @wholeSearch + '%', descript)<> 0 set @loop = 0End while @loop > 0begin 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 endINSERT INTO #results(resultsID, descript )select * from #bigTemp where patindex('%' + @wholeSearch + '%', descript)<> 0INSERT INTO #fullResults(resultsID, hits)sELECT resultsID, COUNT(*) as 'Hits' FROM #results GROUP BY resultsIDUPDATE ASET A.descript = B.descriptFROM #fullResults as a, #results as BWHERE A.resultsID = B.resultsIDselect resultsID as ID_Key, hits as Hits, descript as Blurb from #fullResults order by hits descdrop 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 intDeclare @parse intDeclare @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 ' + @tableexec sp_executesql @cmdset @loop = 1while @loop > 0begin 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 endINSERT INTO #results(resultsID, descript )select resultsID, cast(descript as varchar(140)) from #bigTemp where patindex('%' + @wholeSearch + '%', descript)<> 0INSERT INTO #fullResults(resultsID, hits)sELECT resultsID, COUNT(*) as 'Hits' FROM #results GROUP BY resultsIDUPDATE ASET A.descript = B.descriptFROM #fullResults as a, #results as BWHERE A.resultsID = B.resultsIDselect resultsID as ID_Key, hits as Hits, descript as Blurb from #fullResults order by hits descdrop table #results drop table #fullResults drop table #bigTemp GO |
|
|
|
|
|
|
|