"sqlster" wrote:

> Currently I have check like the following going on in the WHERE CLAUSE and to
> me it could be written better.
> ---
>         (mydate is null or mydate is not null) and (@only_show_dates is null)
>         or
>         (mydate is not null) and (@only_show_dates is not null)
>
> -----
>
> set nocount on
> go
> create table z_test_del
> (
>     mydate datetime,
>     mynum int
> )
> go
> insert z_test_del values('1/1/1992',1)
> insert z_test_del values('1/1/1993',2)
> insert z_test_del values(null,3)
> insert z_test_del values('1/1/1994',4)
> insert z_test_del values('1/1/1995',5)
> insert z_test_del values(null,6)
> go
> declare @only_show_dates bit
> set @only_show_dates=1
>
> select *
> from z_test_del
> where
>         (mydate is null or mydate is not null) and (@only_show_dates is null)
>         or
>         (mydate is not null) and (@only_show_dates is not null)
>
> set @only_show_dates=null
>
> select *
> from z_test_del
> where
>         (mydate is null or mydate is not null) and (@only_show_dates is null)
>         or
>         (mydate is not null) and (@only_show_dates is not null)
>
>
> go
> drop table z_test_del
> ===
>
> The resultset is
>
> mydate                                                 mynum      
> ------------------------------------------------------ -----------
> 1992-01-01 00:00:00.000                                1
> 1993-01-01 00:00:00.000                                2
> 1994-01-01 00:00:00.000                                4
> 1995-01-01 00:00:00.000                                5
>
> mydate                                                 mynum      
> ------------------------------------------------------ -----------
> 1992-01-01 00:00:00.000                                1
> 1993-01-01 00:00:00.000                                2
> NULL                                                   3
> 1994-01-01 00:00:00.000                                4
> 1995-01-01 00:00:00.000                                5
> NULL                                                   6
>
>
>

"sqlster" wrote:

> Please let me know if there is a better way to write the where clause.
>
> "sqlster" wrote:
>
> > Currently I have check like the following going on in the WHERE CLAUSE and to
> > me it could be written better.
> > ---
> >         (mydate is null or mydate is not null) and (@only_show_dates is null)
> >         or
> >         (mydate is not null) and (@only_show_dates is not null)
> >
> > -----
> >
> > set nocount on
> > go
> > create table z_test_del
> > (
> >     mydate datetime,
> >     mynum int
> > )
> > go
> > insert z_test_del values('1/1/1992',1)
> > insert z_test_del values('1/1/1993',2)
> > insert z_test_del values(null,3)
> > insert z_test_del values('1/1/1994',4)
> > insert z_test_del values('1/1/1995',5)
> > insert z_test_del values(null,6)
> > go
> > declare @only_show_dates bit
> > set @only_show_dates=1
> >
> > select *
> > from z_test_del
> > where
> >         (mydate is null or mydate is not null) and (@only_show_dates is null)
> >         or
> >         (mydate is not null) and (@only_show_dates is not null)
> >
> > set @only_show_dates=null
> >
> > select *
> > from z_test_del
> > where
> >         (mydate is null or mydate is not null) and (@only_show_dates is null)
> >         or
> >         (mydate is not null) and (@only_show_dates is not null)
> >
> >
> > go
> > drop table z_test_del
> > ===
> >
> > The resultset is
> >
> > mydate                                                 mynum      
> > ------------------------------------------------------ -----------
> > 1992-01-01 00:00:00.000                                1
> > 1993-01-01 00:00:00.000                                2
> > 1994-01-01 00:00:00.000                                4
> > 1995-01-01 00:00:00.000                                5
> >
> > mydate                                                 mynum      
> > ------------------------------------------------------ -----------
> > 1992-01-01 00:00:00.000                                1
> > 1993-01-01 00:00:00.000                                2
> > NULL                                                   3
> > 1994-01-01 00:00:00.000                                4
> > 1995-01-01 00:00:00.000                                5
> > NULL                                                   6
> >
> >
> >