use optional parameter in WHERE clause

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)

use optional parameter in WHERE clause

Author

Topic

GrandVizier
Starting Member

14 Posts

Posted - 2007-03-23 : 13:14:57

I'm running a sql statement that takes in a parameter to filter for 'tID'. However its possible for the user not to filter the results, thereby selecting all rows from table 'Tour'. How do I include the option for selecting ALL records?


Select * from Tour
Where (tDeleted = 0) and CASE 
  WHEN @param1 THEN (tID = ?ALL?)
	ELSE tID = @param1 
END

There is some bad syntax in that option, but I've tried this as well:


Select * from Tour
Where (tDeleted = 0) and tID IN (coalesce(@param1, ?ALL?))

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-23 : 14:16:43

see below...post # 2...got by using the search facility (optional parameter)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41203&SearchTerms=optional,parameter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources