Overriding data precedence in a COALESCE expression

Question

Overriding data precedence in a COALESCE expression

Stephanie Giovannini

SSCertifiable

Points: 7422
More actions
August 5, 2015 at 9:35 am

#299387

I want to return a BIT from an outer join. I want the result type to be BIT. However, the usage of ISNULL is forbidden by code standards. The first expression returns an INT. Which of the bottom expressions do you think would work best in a query?
And why would ISNULL be absolutely forbidden? I was told that Microsoft deprecated it :blink: along with TINYINT, which I'm often prevented from using even when it's the appropriate data type.
DECLARE @x BIT;
DECLARE @default BIT = 1;
DECLARE @something sql_variant;
SELECT @something = COALESCE(@x, 1);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
SELECT @something = COALESCE(@x, @default);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
SELECT @something = COALESCE(@x, CAST(1 AS BIT));
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Stephanie Giovannini (8/5/2015)
I want to return a BIT from an outer join. I want the result type to be BIT. However, the usage of ISNULL is forbidden by code standards. The first expression returns an INT. Which of the bottom expressions do you think would work best in a query?
And why would ISNULL be absolutely forbidden? I was told that Microsoft deprecated it :blink: along with TINYINT, which I'm often prevented from using even when it's the appropriate data type.
DECLARE @x BIT;
DECLARE @default BIT = 1;
DECLARE @something sql_variant;
SELECT @something = COALESCE(@x, 1);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
SELECT @something = COALESCE(@x, @default);
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');
SELECT @something = COALESCE(@x, CAST(1 AS BIT));
SELECT SQL_VARIANT_PROPERTY(@something, 'BaseType');

I'd probably go with the second of the three. The only reason I could think of that your coding standards won't allow you to use ISNULL is that it is not a SQL Standard function where COALESCE is a SQL Standard function.

I have not heard that ISNULL and TINYINT have been deprecated. I'll have to look into that.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Couldn't find anything that says ISNULL or TINYINT are being deprecated.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

I agree. Neither of those are on the deprecation list.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 4

I used the second solution. With the proper names, it even documents the query nicely. The bit values are allowed or not allowed values.

So, I used this:

DECLARE @allowed BIT = 1;

DECLARE @x BIT;

SELECT COALESCE(@x, @allowed) AS x_allowed;