Post reply

Overriding data precedence in a COALESCE expression

  • 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');

  • August 5, 2015 at 9:43 am

    #1817605

    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.

  • August 5, 2015 at 10:31 am

    #1817633

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

  • August 6, 2015 at 6:45 am

    #1817828

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 6, 2015 at 1:15 pm

    #1817963

    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;

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

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