Db Queries and Recordsets - 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)

Db Queries and Recordsets

Author

Topic

mazza
Starting Member

3 Posts

Posted - 2004-07-06 : 09:07:55

I am trying to include a search and results on a website. The search, by inputting 1(one) parameter into the search page (HTML form) should check each table column for this word (so the parameter could be found in any column) then the results page to show only those rows that contain the word in ANY of the columns.
The code I have is something like:

SELECT NickName, Name, Address, Region, Country, Photograph, Details, TelNo, MobileNo
FROM Contacts
WHERE Country LIKE 'varCountry' OR NickName LIKE 'varNickName' OR Name LIKE 'varName' OR Address LIKE 'varAddress' OR Region LIKE 'varRegion'

then the run time variable setup as
varCountry % Request.QueryString("nameofsearchform")
varNickName etc...
But this does not return results as expected. Could someone please tell me where I amgoing wrong?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 09:22:04

You need to have LIKE '%varCountry%'.

You do know this will run slow as molasses in winter, right?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mazza
Starting Member

3 Posts

Posted - 2004-07-06 : 09:38:00

Thanks for your help. I will try this.
Why will it run so slow? Can I make the process any quicker? I have only used examples from my web developing app Help section. It is the first time I have tried to include anything like this in a website.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 17:23:47

It will be slow because it won't use indexes properly with the wildcard in front. You need to make them at least enter the first part properly. For example, for country they can enter uni which would return United States and United Kingdom. The more specific you can get, the faster things will run. Country should really be a drop-down list so you don't even have to use LIKE for example.

Also, you need to make this a Stored Procedure and call the stored procedure instead of creating the whole statement in your code.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources