SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 cleaning up my search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mleduc
Starting Member

2 Posts

Posted - 08/26/2005 :  13:10:31  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000