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 2005 Forums

Transact-SQL (2005)

search query

Author

Topic

aneeshmm
Starting Member

20 Posts

Posted - 2009-02-13 : 23:55:43

hi everyone...
i have an advanced search page with about 10 fields to search the db. I want the results to come like: the records satisfying all the criteria first and so on. Is there any way to perform this in one query..or will i have to write separate queries. Please advice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 00:31:43

yup. you can use conditioanl where clauses like
where (col1 =@param1 or @param1 is null)
and (col2 =@param2 or @param2 is null)
....

and make default values of all parameters as null

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-14 : 01:13:19

as he wants the results to be ordered based on the criterias use something like

where	(col1 =@param1 or @param1 is null)
		and (col2 =@param2 or @param2 is null)
		....
order by col1 desc, 
		col2 desc,
		...

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 01:22:54

or did you meant this?


where col1 =@param1 
or col2 =@param2 
or col3=@param3
or col4=@param4
....

order by nullif(col1,@param1),nullif(col2,@param2),nullif(col3,@param3),nullif(col4,@param4),...

aneeshmm
Starting Member

20 Posts

Posted - 2009-02-14 : 02:31:05

thnx for the replies... i couldnt try this, i'll try and reply soon... thanks again friends...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 02:34:21

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources