NULL handling in variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NULL handling in variables

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-30 : 22:47:31

Hi,

Please help in this.

if i am throwing a set of results on SQL into variables e.g. @ID of INT
and then using these variables into WHERE clauses.

e.g. WHERE H.specialty_id = @ID

but some rows have NULL value
so i cant match WHERE H.specialtY_ID = NULL
instead need to be WHERE H.specialty_ID is NULL
how do I structure my query to allow it?
so some variables will have values some will have NULL

Thanks,
Gangadhar

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-01 : 00:08:30

can use ISNULL() or COALESCE() functions

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-10-01 : 00:15:06

I am using this query

declare @a int
set @a=NULL
select * from wages where num_sales=@a

Its not returning the rows when @s is NULL,

If it is other integer values its returning rows.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-10-01 : 00:17:24

i want to format a WHERE clause
so that
when @SID = NULL
it'll be
Select * from HCP where specialty_ID is NULL
and when @SID is not NULL
Select * from HCP where specialty_ID = @SID

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 02:17:23

[code]
Select * from HCP where
(specialty_ID is NULL and @sid is null)
or
(specialty_ID is not NULL and @sid is not null)
[/code]

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-01 : 12:32:31

Maybe:

declare @a int
set @a=NULL
select * 
from wages 
where 
    num_sales = @a
    OR
    (
        @a IS NULL
        AND num_sales IS NULL
    )

-- or if you wanted to increase performance

declare @a int
set @a=NULL

if (@a is null)
begin
    select * from wages where num_sales = @a
end
else
begin
    select * from wages where num_sales IS NULL
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources