Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stamford
Starting Member

United Kingdom
47 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
Aged Yak Warrior

USA
550 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  
 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.16 seconds. Powered By: Snitz Forums 2000