SQLTeam.com | Weblogs | Forums

Parameter in Where clause


#1

Hello,

I have a SQL query that I am using as a command in a Crystal Report. I would like to use a parameter to let a user choose between a version of the report where a field is null or one where the field is populated. Right now the Where clause is:

**WHERE **
earr.EVENT_TIME >= @BeginDate and earr.EVENT_TIME <= @EndDate
and earr.EVENT_TIME is not null
and loc.loc_id in {?Facility}
and isnull(enc.INP_ADM_DATE, enc.OP_ADM_DATE ) is not null

I would like to change the last line, and isnull(enc.INP_ADM_DATE, enc.OP_ADM_DATE ) is not null, to one where a parameter would allow a user to choose whether this field is null or not. How can this be done. Thank you very much in advance for any help!


#2

Add a boolean parameter:

@CheckForNull bit = 0

then in your where clause

where ... and (@checkForNull = 1 and isnull(enc.INP_ADM_DATE, enc.OP_ADM_DATE ) is not null or @CheckForNull = 0 )


#3

Thank you very much! It looks like that works!