Conditional where clause - 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 2008 Forums

Transact-SQL (2008)

Conditional where clause

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-06-08 : 21:42:06

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-09 : 11:26:17

I think what you are trying to do is a catch-all query. You can do it with plain SQL or Dynamic SQL. Here is an article discussing some of the options and their performance implications:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

What do you want to have happen when a parameter is NULL? Do, you want all rows? I guess I'm just not clear on how you want to handle NULLs.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-09 : 11:33:58

If you wanted a direct translation of that case expression, then it would be something like:

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-09 : 15:08:33

If @StartDate and/or @EndDate may not be a valid date, then you need to use this approach:



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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-09 : 16:04:07

quote:

Originally posted by ScottPletcher

If @StartDate and/or @EndDate may not be a valid date, then you need to use this approach:



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

It's be tough to have a datetime that isn't a valid date. ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources