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