CODEWITHSUNDEEP
sqlsql-serversql-server-2005
stonypaul
stonypaul

Reputation: 695

Conditional where clause using non-NULL parameters

I'm trying to write a script with a conditional where clause. pretty much like this. is this do-able? this is a stored procedure which will be called by a vb.net application and the value of @ModeType will determine the parameters to be used in the where clause. i will pass dbnull values to the unused parameters where necessary.

CREATE PROCEDURE [dbo].[newNBOCAP_DATA_UPDATE]
@StartDate datetime,
@EndDate datetime,
@ReferralID integer,
@ModeType varchar (50)

select * from tableA
where
case
when @ModeType = 'proforma' and @ReferralID IS NOT NULL
then referral = @ReferralID
when @ModeType = 'download' and ISDATE(@StartDate) = 1 and ISDATE(@EndDate) = 1
then startdate = @StartDate and enddate = @EndDate
end

Upvotes: 0

Views: 65

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

People always seem to rush to try to use CASE when in fact all they need is basic boolean logic:

select * from tableA
where
(@ModeType = 'proforma' and @ReferralID IS NOT NULL and referral =@ReferralID) or
(@ModeType = 'download' and ISDATE(@StartDate) = 1 and ISDATE(@EndDate) = 1 and
    startdate = @StartDate and enddate = @EndDate)

Although, of course, referral =@ReferralID can never be true if @ReferralID is NULL, unless ANSI_NULLS is off which is not recommended. Also, variables declared as datetime can only be valid dates or NULL, so we can probably simplify further to:

select * from tableA
where
(@ModeType = 'proforma' and referral =@ReferralID) or
(@ModeType = 'download' and startdate = @StartDate and enddate = @EndDate)

Upvotes: 4

Related Questions