IF Statement in SELECT 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)

IF Statement in SELECT WHERE Clause

Author

Topic

robson
Starting Member

22 Posts

Posted - 2008-06-12 : 10:14:42

Hi Everyone,

I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:

IF @categoryID <> 12
AND (category.categoryID = @categoryID)

STORED PROCEDURE:

CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS

SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 10:23:37

use

WHERE CASE WHEN @categoryID <> 12 THEN ... ELSE... END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 10:25:40

You can have two select statement in IF ELSE block or

CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS

SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = case when @categoryID<>12 then @categoryID else category.categoryID end)

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-12 : 10:26:25

You can use case statement.

CASE WHEN @categoryID <> 12
AND (category.categoryID = @categoryID) THEN ..... END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 10:27:53

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources