CODEWITHSUNDEEP
sql-server
Sandy
Sandy

Reputation: 760

ISNULL not working correctly for money data type - SQL Server

DECLARE @V_FEE MONEY = 0.00
IF ISNULL(@V_FEE,'')=''
   SELECT 'FAIL'
ELSE
   SELECT 'PASS'

Why its returning 'FAIL' even my fee amount it 0.00. I am facing this issue in SQL Server.

Upvotes: 0

Views: 2132

Answers (4)

Quethzel Diaz
Quethzel Diaz

Reputation: 641

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

Check it on Data Type Precedence MSDN.

Upvotes: 2

Magnus Alexander
Magnus Alexander

Reputation: 96

I think this happening because you are using a different type on isnull, you are passing a money type and you are using a varchar as default value. If you use same types on ISNULL you receive PASS answer on select. Try this:

DECLARE @V_FEE MONEY = 0.00
IF ISNULL(@V_FEE, -1) = -1
      SELECT 'FAIL'
ELSE
     SELECT 'PASS'

Upvotes: 4

SqlZim
SqlZim

Reputation: 38043

Because '' converts to 0.0000 when converted to money. 

select convert(money,'')

returns: 0.0000

declare @v_fee money = 0.00
if @v_fee is null
   select 'fail'
else
   select 'pass'

returns: pass

Upvotes: 1

McNets
McNets

Reputation: 10807

Try this:

DECLARE @V_FEE MONEY = NULL

IF ISNULL(@V_FEE,0.00) = 0.00
   SELECT 'PASS'
ELSE
   SELECT 'FAIL'

In fact if you try:

DECLARE @V_FEE MONEY = 0.00;

SELECT ISNULL(@V_FEE, 'AAA');

It returns:

0.00

Upvotes: 0

Related Questions