MS SQL Server - isnull logic changes

Author

isnull logic changes

jobs

2006-11-15, 7:14 pm

using asp.net sqldatasource selectcommand to populate a datagrid.

I have a parameter that might be empty or null.

How can change logic based on it having a value or not..

for example

select * from table where x=@x and y=@y

how can I code my sql, so that if @ is null that part of the where
clause has no impact and is essentially

select * from table where x=@x

Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA

2006-11-15, 7:14 pm

Try

select * from table where x = ISNULL(@x,x) and y = ISNULL(@y,y)

"jobs" wrote:

> using asp.net sqldatasource selectcommand to populate a datagrid.
>
> I have a parameter that might be empty or null.
>
> How can change logic based on it having a value or not..
>
> for example
>
> select * from table where x=@x and y=@y
>
>
> how can I code my sql, so that if @ is null that part of the where
> clause has no impact and is essentially
>
> select * from table where x=@x
>
>

jobs

2006-11-15, 7:14 pm

Thank you that worked, but my problem is now that the textbox i'm using
for x@, though blank is really not NULL and not sure how check if blank
''.

thanks for your response!

Edgardo wrote:[color=darkred
]
> Try
>
> select * from table where x = ISNULL(@x,x) and y = ISNULL(@y,y)
>
> "jobs" wrote:
>

Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA

2006-11-15, 7:14 pm

Then you can use

select * from table
where x = (case when @x = '' then x else @x end)
and
y = (case when @y = '' then y else @y end)

"jobs" wrote:

> Thank you that worked, but my problem is now that the textbox i'm using
> for x@, though blank is really not NULL and not sure how check if blank
> ''.
>
> thanks for your response!
>
> Edgardo wrote:
>
>

jobs

2006-11-15, 7:14 pm

Nice. That opens all sort of options. Thank you!

Edgardo wrote:[color=darkred
]
> Then you can use
>
> select * from table
> where x = (case when @x = '' then x else @x end)
> and
> y = (case when @y = '' then y else @y end)
>
>
> "jobs" wrote:
>

jobs

2006-11-16, 7:12 pm

I might be Stretching the limits of this to avoid codebehind .. perhap
my approach is off..

I'm trying to get a filtering effect, where the more that is passed the
further we

The idea being that when you something is set to 9999 is set it's
because 'ALL' is selected so don't evelute the where.

Declare
@Plancode varchar(10),
@routecode varchar(10),
@countryid int,
@dates varchar(10),
@dest varchar(10)

Set @CountryId = 9999
Set @Plancode = '9999'
Set @RouteCode = '999'
Set @Dates = 'None'
Set @Dest = 'None'

SELECT * FROM [XXX] WHERE plancode = (Case when @CountryId='9999'
then plancode else @plancode end) and RouteCode =(Case when
@RouteCode='9999' then RouteCode else
@RouteCode end) and CountryId=(Case when @CountryId='9999' then
CountryId else @CountryId end)
and startdate<=(case when @Dates='None' then startdate else cast(@Dates
as datetime) end)
and enddate>=(case when @Dates='None' then enddate else cast(@Dates as
datetime) end) and
DestCode like (case when @Dest='None' then DestCode else '%'+@Dest+'%'
end) order by
StartDate

Sponsored Links