WHERE statement

[MrMystery]

Is it possible to build a condition into an SQL where statement to perform a different selection dependant on the value of a selection parameter?
e.g.
Selection Parameter = JobNo
Value can be either a job number or 'ALL'

If I enter a job number, then my 'WHERE' statement must only select values from the table matching this job number, EXCLUDING NULL values.
If I enter 'ALL' then my 'WHERE' statement must select ALL records, including NULL values.

Any help appreciated!

Mike

 

[JamesLean]

Have a look at this thread to start you off:

thread183-508649 --James

 

[tlbroadbent]

A simple query should give you what you want.

Create Proc uspFindJob
@job varchar(8)
As

Select *
From JobTable
Where JobNumber=@JobNo
Or @jobNo='ALL' If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm