How to get records whcih are null in some column

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to get records whcih are null in some column

Author

Topic

raviborra
Starting Member

14 Posts

Posted - 2008-02-13 : 13:06:55

Hi,

This is the table which i have.

1 100 Ravi IT 20000
2 200 SitaRam CSE 30000
3 300 Rams EEE NULL
5 400 Kabir IT NULL

And part of my SP has as follows.

DECLARE @salary INT;
SET @salary=NULL;
DECLARE @DeptName VARCHAR(20);
SET @DeptName='IT';
IF EXISTS(SELECT * FROM Employee WHERE DeptName = @DeptName AND salary = @salary)
BEGIN
print 'Record existed in the table'
END
ELSE
BEGIN
print 'Record not existed in the table'
END

Actually record is existed with that combination, how this query will work seemlessly in all the situation.

Meaning, how to check for NULL existance with out using "IS NULL"

Thanks,
Ravi.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-13 : 13:13:53

declare @Employee table (
c1 int, c2 int, EmpName varchar(200), DeptName varchar(200), salary numeric(10,2)
)

insert into @Employee
select 1 , 100 , 'Ravi', 'IT', 20000 union
select 2, 200, 'SitaRam', 'CSE', 30000 union
select 3, 300, 'Rams', 'EEE', NULL union
select 5, 400, 'Kabir', 'IT', NULL

select * from @Employee

DECLARE @salary INT;
SET @salary=NULL;
DECLARE @DeptName VARCHAR(20);
SET @DeptName='IT';
IF EXISTS(SELECT * FROM @Employee WHERE DeptName = @DeptName AND IsNull(salary,0) = IsNull(@salary,0))
BEGIN
print 'Record existed in the table'
END
ELSE
BEGIN
print 'Record not existed in the table'
END

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

raviborra
Starting Member

14 Posts

Posted - 2008-02-13 : 13:20:01

Hi,

Thanks a lot; It works.

Thanks,
Ravi.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 04:21:52

Why dont you use this?

IF EXISTS(SELECT * FROM Employee WHERE DeptName = @DeptName AND (@salary is null or salary = @salary)

Madhivanan

Failing to plan is Planning to fail

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-14 : 08:33:14

If you set @salary to null and the salary in the DB is not null, then you will still get results
((@salary is null AND salary is NULL) or salary = @salary)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources