| Author |
 Topic  |
|
|
mleduc
Starting Member
2 Posts |
 Posted - 08/26/2005 : 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 - 08/26/2005 : 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 |
 |
|
| |
Topic |
|
|
|
cleaning up my search
