Check for NULLS

Question

Post reply

Check for NULLS

Aurora

Old Hand

Points: 324
More actions
January 14, 2003 at 7:17 am

#80609

Hi all,
This is a strange one. I have a stored proc that in the body has 3 checks against the variables (being passed)for NULL's. The column (types) are uniqueidentifiers. When this section is commented out the sproc runs in 4 secs. When I UNcomment it, it tacks on an additional 7 secs. Does anyone know why?
I haven't rulled out that the sproc could be rewritten, but in all honesty, its only checking for NULL values, if NULL THEN do something. There are no NULLS being passed in the example I have illustrated. Wierd right?
Help!
Edited by - aurora on 01/14/2003 07:18:01 AM

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

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

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

Does anything happen in the ELSE block?

Regards,

Andy Jones

.

Aurora Old Hand Points: 324 More actions · Answer 2

Yes, IF the column is NOT NULL THEN an INSERT BEGINS.

However, my test case is passing a null, so no insert should take place but it takes an extra 7 seconds to perform the check maybe? HmmmH?

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 3

Checking the destinations, are the insert(s) actually taking place?

How are you checking for null? See the following article to see if it is behaving as you are expecting.

http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

Regards,

Andy Jones

.

Aurora Old Hand Points: 324 More actions · Answer 4

I am checking to see IF @variable IS NULL.

=============================================

Thanks for the article, it explained why it behaved differently when I turned ON/OFF ANSI_NULL.

=============================================

Is there anyway I can improve the performance of this block?

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 5

What is happening in the block? Can you post the code.

Regards,

Andy Jones

.

Aurora Old Hand Points: 324 More actions · Answer 6

The sproc performs an INSERT into a dbo.table if the @variable is NOT NULL. However, its not performing the insert (the value being passed is NULL. So it should continue. When the block of code (which simply checks for the NOT NULL)is commented out it takes 4 secs. Uncomment it, it takes 12 secs. I'll grab the code and post it.

Edited by - aurora on 01/14/2003 12:06:28 PM

tkuhns Newbie Points: 8 More actions · Answer 7

I've run into that before also.

I've gotten into the habit of using something like:

IsNULL(fieldname,'x')='x'

HTH

Todd

Aurora Old Hand Points: 324 More actions · Answer 8

Thanks for all your help and responses. I'll keep you posted on the progress.

Aurora Old Hand Points: 324 More actions · Answer 9

Thanks for all your help and responses. I'l keep you posted on the progress.