Search Query - 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

Transact-SQL (2000)

Search Query

Author

Topic

Kirun
Yak Posting Veteran

60 Posts

Posted - 2010-01-27 : 06:26:07

I want users to give a search mechanism for 3 feilds.

I am wrting first a selct query in which where is like this



WHERE     (Source.SourceTitle LIKE N'%' + ? + N'%') OR
                      (Subject.SubjectTitle LIKE N'%' + ? + N'%') OR
                      (eJournal.eJournalTitle LIKE N'%' + ? + N'%')

Now when i am trying to search for Subject,It also searches in the title and source.How can i make a query that when i give a value for source it should search only in source colum.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-27 : 06:50:45

Change OR to AND?

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-27 : 08:26:09

I think you'll have to give a worked example.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kirun
Yak Posting Veteran

60 Posts

Posted - 2010-01-28 : 01:44:19

Changing to AND will result in no search result ..... should i be writing a dynamic query or stored procedure ?

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 03:46:50

If you have "optional" search parameters send them as NULL if they are not required, and then use this syntax:


WHERE     (?1 IS NULL OR Source.SourceTitle LIKE N'%' + ?1 + N'%')
      AND (?2 IS NULL OR Subject.SubjectTitle LIKE N'%' + ?2 + N'%')
      AND (?3 IS NULL OR eJournal.eJournalTitle LIKE N'%' + ?3 + N'%')

I don't know how your parameter substitution works, so I've labels the parameters ?1, ?2 and ?3 so you can see where each is duplicated.

You might be better off (performance wise) using a Free Text search, instead of LIKE as "LIKE '%foo%'" will be very CPU/Disk intensive.

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 03:48:07

P.S. You could send them as EMPTY STRING rather than NULL and use


    (?1 = '' OR ...
AND (?2 = '' OR ...
...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 04:11:04

quote:
Originally posted by Kirun

I want users to give a search mechanism for 3 feilds.

I am wrting first a selct query in which where is like this

WHERE (Source.SourceTitle LIKE N'%' + ? + N'%') OR
(Subject.SubjectTitle LIKE N'%' + ? + N'%') OR
(eJournal.eJournalTitle LIKE N'%' + ? + N'%')

Now when i am trying to search for Subject,It also searches in the title and source.How can i make a query that when i give a value for source it should search only in source colum.

Which RDBMS are you using?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 05:58:10

quote:
Originally posted by Kirun

I want users to give a search mechanism for 3 feilds.

I am wrting first a selct query in which where is like this

WHERE (Source.SourceTitle LIKE N'%' + ? + N'%') OR
(Subject.SubjectTitle LIKE N'%' + ? + N'%') OR
(eJournal.eJournalTitle LIKE N'%' + ? + N'%')

Now when i am trying to search for Subject,It also searches in the title and source.How can i make a query that when i give a value for source it should search only in source colum.

you've different parameters for passing each field values?

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 07:23:41

Some database drivers do a substitution of parameters, using "?" as place-markers, don't they? ODBC springs to mind ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 10:03:43

quote:
Originally posted by Kristen

Some database drivers do a substitution of parameters, using "?" as place-markers, don't they? ODBC springs to mind ...

I think MS ACCESS also does it

Madhivanan

Failing to plan is Planning to fail

Kirun
Yak Posting Veteran

60 Posts

Posted - 2010-02-17 : 04:18:14

i am using SQL 2005 and yes i am passing diffrent parameter for each field.. is my query is bit old fashioned or what... doesnt seems to understand.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 04:22:20

quote:
Originally posted by Kirun

i am using SQL 2005 and yes i am passing diffrent parameter for each field.. is my query is bit old fashioned or what... doesnt seems to understand.

where have you written the above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

venkat09
Starting Member

17 Posts

Posted - 2010-03-02 : 04:50:35

You might use another input argument to your stored procedure which distinguishes your search.
Like:

Create sp_BLABLA
( @SearchStr1 nvarchar(100), @SearchStr2 nvarchar(100), @SearchStr3 nvarchar(100), @SearchBy int )
as

select .....
from Source
left join (/ full join) Subject
left join (/ full join) eJournal

where (@SearchBy = 1 and Source.SourceTitle LIKE N'%' + @SearchStr1 + N'%') OR
(@SearchBy = 2 and Subject.SubjectTitle LIKE N'%' + @SearchStr2 + N'%') OR
(@SearchBy = 3 and eJournal.eJournalTitle LIKE N'%' + @SearchStr3 + N'%') OR
(@SearchBy = 4 and (Source.SourceTitle LIKE N'%' + @SearchStr1 + N'%' or Subject.SubjectTitle LIKE N'%' + @SearchStr2 + N'%' or eJournal.eJournalTitle LIKE N'%' + @SearchStr3 + N'%') )

GO

Venkat R. Prasad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources