SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Conditional where clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
42 Posts

Posted - 06/08/2014 :  21:42:06  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/09/2014 :  11:26:17  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/09/2014 :  11:33:58  Show Profile  Reply with Quote
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
)
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
362 Posts

Posted - 06/09/2014 :  15:08:33  Show Profile  Reply with Quote
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)

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/09/2014 :  16:04:07  Show Profile  Reply with Quote
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. ;)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000