Simple Casting Question Using ISNULL 3

[Auguy]

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?

SELECT ISNULL(UPL.SortNbr, 0) AS SortNbr

Also, what about

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

 

[DaveInIowa]

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

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

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

 

[Auguy]

Thanks Dave, I will follow your guidelines.

Auguy
Sylvania/Toledo Ohio

 

[Olaf Doschke]

First about ISNULL:

https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql

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/language-elements/expressions-transact-sql

and

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql

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.

 

[gmmastros]

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/ms190309(v=sql.110).aspx

To see this in action...

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?

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

 

[gmmastros]

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:

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

 

[Auguy]

Wow, good stuff. Thanks to all who contributed!

Auguy
Sylvania/Toledo Ohio