Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

SQL - Now you see me, now you don't

, 14 May 2012
Rate this:
Please Sign up or sign in to vote.
ISNULL - COALESCE

Introduction

SQL Server, undoubtedly my favourite Microsoft product, but a seasoned baseball pitcher will be impressed by the curve balls it can produce.

Using the code 

Execute the following script:

declare @var1 VARCHAR(1)
SET @var1 = '2'
SELECT ISNULL(@var1, 1)
SELECT ISNULL(@var1, -1)
SELECT COALESCE(@var1, -1)
SET @var1 = NULL
SELECT ISNULL(@var1, 1)
SELECT ISNULL(@var1, -1)
SELECT COALESCE(@var1, -1)

Sure, it is easy to see the culprit, but now read this thinking that @var1 is a column defined in a table, so it is not so obvious what the length of the column/variable is.

  • ISNULL – replaces the NULL value with the given replacement.
  • COALESCE – returns the first not NULL expression in the argument list.

When reading the MSDN topic on T-SQL ISNULL it does say that it returns the SAME type as the checked expression, the value is IMPLICITLY converted to the checked expression’s type when the two types differ.

The interesting part is that the conversion does not procedure a String or binary data will be truncated error?

This does not prove that COALESCE is better than ISNULL, all it is saying: know the animal you are working with.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Programm3r
Software Developer (Senior) Kaizen-IT
South Africa South Africa
I escaped from the mental hospital on 25th June and was captured by a zookeeper. Escaped from the zoo on 15th July and killed the zoo guard in the attempt. So now I just eat bananas and hang out on the Code Project.
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralThoughts PinmemberPIEBALDconsult14-May-12 9:30 
What are you on about? I think you should explain more clearly exactly what it is you're trying to communicate.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 14 May 2012
Article Copyright 2012 by Programm3r
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid