CODEWITHSUNDEEP
sql-serverisnull
loyalflow
loyalflow

Reputation: 14879

When using ISNULL, you can potentially return 2 different types

Given the following example:

declare @t1 AS VARCHAR(10)
SET @t1 = 'asdf'

SELECT ISNULL(@t1, 0) AS test1

Now if @t1 was NULL, the call to ISNULL would return 0 which is an INT.

So this means you have 2 potential data types being return for the same column correct?

Upvotes: 1

Views: 493

Answers (1)

M.Ali
M.Ali

Reputation: 69524

This will work fine Sql server does am implicit Conversion and converts 0 into string '0' because of the datatype of the column being returned.

DECLARE @Test_TABLE TABLE(Column1 VARCHAR(10))
INSERT INTO @Test_TABLE VALUES
('ALPHA'),(NULL),('Beta'),('Gemma')

SELECT ISNULL(Column1, 0)
FROM @Test_TABLE

This will throw an error as when sql server tries to convert to the datatype of the column which is INT it fails and it throws an error 

DECLARE @Test_TABLE1 TABLE(Column1 INT)
INSERT INTO @Test_TABLE1 VALUES
(1),(NULL),(2),(3)

SELECT ISNULL(Column1, 'Is null')
FROM @Test_TABLE1

Msg 245, Level 16, State 1, Line 5 Conversion failed when converting the varchar value 'Is null' to data type int.

Your can see one datatype takes precedence on other and only One data type is returned not two :)

Upvotes: 1

Related Questions