×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Simple Casting Question Using ISNULL3

## Simple Casting Question Using ISNULL

(OP)
Do I need to Cast the "0" in the following code to an integer. SortNbr is an integer column. Better question should I do it anyway?

#### CODE

SELECT ISNULL(UPL.SortNbr, 0) AS SortNbr

#### CODE

SELECT 0 AS Counter
Should that "0" be cast to an integer too if I want it to be an integer?

Auguy
Sylvania/Toledo Ohio

### RE: Simple Casting Question Using ISNULL

-- If you want to see what the column types will be, you can execute the following script and then check the new table's column attributes to confirm their type.

#### CODE

DECLARE
@NotNullInt	    INT     =	42,
@NullInt	    INT     =	NULL,
@NotNullTinyInt TINYINT =	21,
@NullTinyInt    TINYINT =	NULL

SELECT ISNULL(@NotNullInt, 0) NotNullIntConverted,
ISNULL(@NullInt, 0) NullIntConverted,
ISNULL(@NotNullTinyInt, 0) NotNullTinyIntConverted,
ISNULL(@NullTinyInt, 0) NullTinyIntConverted
INTO MyTestTable 

-- As you'll see, T-SQL is smart enough to create the correct type. In your second example, Counter will be an INT but for readability purposes I've been "strongly-typing" columns in my code like this...

#### CODE

DECLARE
@False          BIT         = 0,
@True           BIT         = 1,
@ZeroInt        INT         = 0,
@ZeroSmallInt   SMALLINT    = 0,
@Etc            VARCHAR(64) = ''

SELECT @False ThisIsFalse, @True ThisIsTrue, @ZeroInt ThisIsAnINT, @ZeroSmallInt ThisIsASmallInt, @Etc ThisIsAVarchar64 

### RE: Simple Casting Question Using ISNULL

(OP)

Auguy
Sylvania/Toledo Ohio

### RE: Simple Casting Question Using ISNULL

https://docs.microsoft.com/en-us/sql/t-sql/functio...

#### Quote (MS Docs)

Syntax: ISNULL ( check_expression , replacement_value )

...

Return Types
Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the data type of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

In your case, the first sentence already covers you: "Returns the same type as check_expression.", as UPL.SortNbr is an int, 0 also will be cast as int automatically. Notice there also is COALESCE, which differs from this.

The second case is less defined. A look into https://docs.microsoft.com/en-us/sql/t-sql/languag... and https://docs.microsoft.com/en-us/sql/t-sql/data-ty... don't really tell what type it would result in. The first link gives an example of 1+2 resulting in 3 - how informative - but doesn't discuss the type. The second link rather tells about the resulting type from an operation on two types, which doesn't cover a simple expression of a single value. I know some languages making a difference of 0 and 0.0, so either you experiment or simply straight forward use CAST(0 as int) as Counter.

Bye, Olaf.

### RE: Simple Casting Question Using ISNULL

This is an interesting question because it highlights a difference between ISNULL and COALESCE.

With IsNull, the return type is decided based on the data type of the first parameter.
With Coalesce, the return type is decided based on SQL Server Data Type Precedence.

** For the rules about data type precedence, take a look here:

https://technet.microsoft.com/en-us/library/ms1903...

To see this in action...

#### CODE

Declare @Age TinyInt;

Set @Age = 47;

Select	SQL_VARIANT_PROPERTY(@Age,'BaseType') As BaseType,
SQL_VARIANT_PROPERTY(Coalesce(@Age, 0),'BaseType') As CoalesceDataType,
SQL_VARIANT_PROPERTY(IsNull(@Age, 0),'BaseType') As IsNullDataType 

Should you convert anyway? Personally, I don't think so because it makes the code harder to read. In the following example, which is easier to read?

#### CODE

SELECT ISNULL(UPL.SortNbr, 0) AS SortNbr
SELECT ISNULL(UPL.SortNbr, Convert(Int, 0)) AS SortNbr 

For "SELECT 0 AS Counter"....

If you want this to be an int, there's no need to cast it because SQL Server will already interpret this as an int. If you want the data type to be something else, then you need to cast/convert it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

### RE: Simple Casting Question Using ISNULL

Olaf,

"When an operator combines two expressions of different data type"

However, this also applies to hard coded values in a query. For example:

#### CODE

Select	SQL_VARIANT_PROPERTY(0,'BaseType')
Select	SQL_VARIANT_PROPERTY(.0,'BaseType') 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

### RE: Simple Casting Question Using ISNULL

(OP)
Wow, good stuff. Thanks to all who contributed!

Auguy
Sylvania/Toledo Ohio

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!