ISNULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ISNULL

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-04 : 04:59:05

Hi

What value will be returned if you were to execute the following statements:


Model 1

   DECLARE @A1 varchar(10), @A2 varchar(12)

   SET @A2 = '12345678912'

   SELECT ISNULL(@A1, @A2)

Model 2

   DECLARE @A1 SMALLINT , @A2 BIGINT

   SET @A2 = 1234567890123456789

   SELECT ISNULL(@A1, @A2)

hello guys please update your technical update about my OP above...

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-04 : 05:09:21

see http://msdn.microsoft.com/en-us/library/ms184325.aspx

quote:

Syntax

ISNULL ( check_expression , replacement_value )

Arguments

check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Return Types

Returns the same type as check_expression.
Remarks

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

KH
[spoiler]Time is always against us[/spoiler]

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-04 : 05:10:23

The reason behind this type of behavior is ISNULL returns the datatype used by first expression as in your example..

@A1 is varchar(10)
so when it is null and have to return the value @A2 so it will return varchar(10) not whole string it is varchar(12)

Same applies to smallint and bigint
its the default behavior by ISNULL
is you take it is

DECLARE @A1 varchar(1), @A2 varchar(12)

SET @A2 = '12345678912'

SELECT ISNULL(@A1, @A2)

then it will return only 1

if you take it as

DECLARE @A1 varchar(12), @A2 varchar(12)

SET @A2 = '12345678912'

SELECT ISNULL(@A1, @A2)

then it will return whole value

ISNULL Default behavior.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources