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

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

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)

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)

---