INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

Simple Casting Question Using ISNULL

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 
Also, what about

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)
Thanks Dave, I will follow your guidelines.

Auguy
Sylvania/Toledo Ohio

RE: Simple Casting Question Using ISNULL

First about 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
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Simple Casting Question Using ISNULL

Olaf,

The second link mentions operations...

"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
twitter
"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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close