Questions on Graz's article Searching on SQLTeam.c

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)

Questions on Graz's article Searching on SQLTeam.c

Author

Topic

Sam Lewitan
Starting Member

3 Posts

Posted - 2002-02-21 : 16:07:09

I read the article but I am left with (at least) 2 problems:
1) The code seems to only create an "or" query how can we make it "and"?
2) Since we are using a created table do I have to worry that it is overwritten by a different user or does the server keep each one separate?

Thanks,
Sam Lewitan

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-21 : 19:22:23

Sam,

1) To make this do AND instead of OR, you would, at a minimum, have to change the INSERT #SearchResults SELECT... statements to use LIKE %1% AND %2% AND %3% AND ... This would probably require parsing the string differently than Graz is, but that's the idea.

2) Temp tables are specific to a SPID (user connection) so they should not overwrite each other between multiple users.

------------------------
GENERAL-ly speaking...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-21 : 20:17:11

There is another way to look at it actually.

If you pass two words to the search, and an article has both those words, it will get inserted to the temp table twice. If it only matches one, it gets inserted once, that is how the ranking works.

So. All you have to do, it filter for only the articles where the count matches the number of distinct words you submitted to the search.

Damian

Sam Lewitan
Starting Member

3 Posts

Posted - 2002-02-25 : 11:37:32

AjarnMark,

quote:

2) Temp tables are specific to a SPID (user connection) so they should not overwrite each other between multiple users.

Is it possible to create a temp stored procedure from asp that wouldn't be overwritten and call it from the same page? This would be another solution b/c I need (I think) to use a stored procedure to get my results so I can use the @@ROWCOUNT to get the record count, you see the other page queries were set up using a technique I found on http://www.adopenstatic.com/experiments/recordsetpaging.asp and I need this one to fit right in. I have discovered that I can create my stored procedure by doing the following:

dim wherewords
'*1)First I send my key words to a function which formats them properly
wherewords = " WHERE (" & KWSearch(Request.QueryString("KWords")) & ") AND (ShowItem = 1) ORDER BY SortOrder "

'*2)then I Alter a stored procedure which I crated for this purposes
ProdText = ProdText & " Alter Procedure ssp_List_Where_@TotRecs (@TotRecs int output) as"
ProdText = ProdText & " SELECT ItemNo, Description, ShowPrice, Price, Avail, smallPic , SpecialPrice ,ETA "
ProdText = ProdText & " FROM Prod " & wherewords
ProdText = ProdText & " select @TotRecs = @@ROWCOUNT return "
rsProd.Open ProdText, strCon, adOpenForwardOnly, adLockReadOnly , adCmdTxt

'*3) lastly I run the new stored procedure
cmdProd.Parameters.Append cmdProd.CreateParameter("@TotRecs", adInteger, adParamOutput, 0)
cmdProd.CommandText = "ssp_List_Where_@TotRecs"
set rsProd = cmdProd.Execute

I'm just afraid that one user will overwrite a different one.

Thanks for all your help,
Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources