Finding NULL values

Question

Post reply

Finding NULL values

karthik M

One Orange Chip

Points: 29139
More actions
October 17, 2008 at 3:45 am

#199879

Hi All,
I have to find NULL values anywhere in the table, If i found any single NULL value in that table, i have to stop the process. Say for example,
drop table #tmp
go
Create table #tmp
(
eno int,
ename varchar(25),
Age int null
)
go
insert into #tmp
select 1,'Karthik',26
union all
select 2,'Keyan',Null
union all
select 3,'Ravi',34
go
How should i find NULL value ?
karthik

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Do you want to report back the "null" record(s) or just that a null exists somewhere in the table?

_____________________________________________________________________
- Nate

@nate_hughes

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Karthick,

1. what are you planning to do once you've found a null and stopped "the process"?

2. what is the process for? Is it for finding nulls?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Is the whole point to remove nulls? If so you need to change the columns so that they do not allow nulls. Finding nulls is easy:

Select

...

From

#tmp

Where

eno is null OR

ename is null OR

age is null

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Karthick,
1. what are you planning to do once you've found a null and stopped "the process"?
2. what is the process for? Is it for finding nulls?

1. Say for example, if any employee has NULL values then i have to raise an error message like 'Data Unavailable for Employee(S) - ' (Employee id's) - one or more. It depends.

2. No. It is not for finding NULL values. Actually i am doing some calculation based on the column values.

Say for example,

EID Ename One_Yr_Gross Two_Yr_Gross Three_Yr_Gross

1 Karthik 45533 24242 343434

2 Raju 87878 Null 767667

If you take ID 2, It has NULL value on Two_yr_Gross column. So i shouldn't do any calculation for that employee(s).

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

And... I have to throw an error message to enduser like 'Upload the correct data into ...'

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Is the whole point to remove nulls? If so you need to change the columns so that they do not allow nulls. Finding nulls is easy:
Select
...
From
#tmp
Where
eno is null OR
ename is null OR
age is null

Jack,

Exactly ! you are correct.I also used the same logic.

But i am thinking, will it leads to any performance related issue in the future ?

Becuase i am using some test data's right now.

But in future, the table data should increase. what do you think ?

karthik

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

I still think the right thing to do is to find the nulls, fix them, and then do not allow nulls in any of the columns you are checking.

How are planning on returning the data? Will you always be returning a single row or will you be returning multiple rows?

If you are returning one row then you can do it in SQL with an IF Exist(Select From table where column is null or column is null...) then RaisError Else return data) of course your performance will deteriorate with the OR's.

If you are returing multiple rows then I think you should do this type of check in the UI as you would have to check the data row by row (RBAR) which is better suited for the UI,.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Jack,

Thanks for your suggestion. Is there any other workaround to check the NULL values (Apart from OR method) ?

Note: My manager said that we should not change the Table structure. We need to do this kind of validation only.

karthik

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

The only way I know other than OR'ing is a different select for each column like:

Select A from table where A is null

Select b from table where B is null

...

I still think you should do this type of check in the UI. Return all your rows and then check for the nulls in the UI and produce the error.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question