CODEWITHSUNDEEP
sqlsql-servert-sql
masroore
masroore

Reputation: 10135

Call scalar function in CASE expression SQL Server 2012+

I am trying to emulate the PL/SQL DECODE() function in T-SQL:

CREATE FUNCTION [dbo].[fnDecode]
(
    @condition     AS BIT,
    @trueVal       AS VARCHAR(2000),
    @falseVal      AS VARCHAR(2000)
)
RETURNS VARCHAR(2000)
WITH EXEC AS CALLER AS
BEGIN
    RETURN CASE @condition
                WHEN 1 THEN @trueVal
                ELSE @falseVal
           END
END
GO

The PL/SQL fragment I'm trying to port to T-SQL is this:

CASE pcr.person_type
WHEN 'N'
THEN
      npcr.first_name
   || DECODE (npcr.middle_name,
              NULL, '',
              ' ' || npcr.middle_name)
   || ' '
   || npcr.last_name
WHEN 'L'
THEN
   lpcr.name
END

I want to call the scalar function defined above in a CASE statement like this:

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + dbo.fnDecode(npcr.middle_name IS NULL, '', ' ' + npcr.middle_name)
      + ' '
      + npcr.last_name
  WHEN 'L' THEN lpcr.name
END

I cannot store the result of the fnDecode() function into a variable because the rows are coming from some joined tables (and there are multiple calls to fnDecode() in the queries I'm porting)

We're using SQL Server 2012 and 2014.

Upvotes: 0

Views: 1396

Answers (2)

IVNSTN
IVNSTN

Reputation: 9318

Porting procedural approaches "as it is" is not a good practice at all. This is not a PHP, C# or even PL/SQL. Furthermore, in the case described you don't even need any function calls. NULL and empty string handling in scenarios mentioned above is very simple and does not require any procedural methods.

There are functions NULLIF, ISNULL, COALESCE, CASE statement. Everything you need is to check if something is true or false, null or not.

Scalar UDF calls for every row may (and will) result in serious performance issues.

Check this out:

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + IsNull(' ' + npcr.middle_name, '')
      + ' '
      + npcr.last_name
  WHEN 'L' THEN lpcr.name
END

even if you have disabled ANSI_NULLS and CONCAT_NULL_YIELD_NULL options and dont' wish to give any chance to server to do anything wrong you can use case and this approach will be much better than calling a function:

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + case when npcr.middle_name is NULL then '' else ' ' + npcr.middle_name end
      + ' '
      + npcr.last_name
  WHEN 'L' THEN lpcr.name
END

if there are empty stings allowed this code can work well (with regular null option set):

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + IsNull(' ' + NullIf(npcr.middle_name, ''), '')
      + IsNull(' ' + NullIf(npcr.last_name, ''), '')
  WHEN 'L' THEN lpcr.name
END

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Yes, it's certainly possible to call scalar functions from within CASE expressions (and many other places).

The issue you're experiencing relates to the first parameter. BIT is a numeric data type. Not a boolean and, in fact, SQL Server does not have an exposed boolean data type in T-SQL. So you cannot make the first parameter of your fDecode function a boolean, nor can you pass the result of evaluating a predicate, such as npcr.middle_name = NULL in a place that expects to accept a bit.

Something like this should execute:

CASE pcr.person_type
  WHEN 'N' THEN npcr.first_name
      + dbo.fnDecode(CASE WHEN npcr.middle_name = NULL THEN 1 ELSE 0 END, '', ' ' + npcr.middle_name)
      + ' '
      + npcr.last_name
  WHEN 'L' THEN lpcr.name
END

(Although you may also need to insert an explicit cast also)

But this makes the cure look worse than the curse.

In any case, you can just use the built in IIF function rather than fDecode. Because it's built in, it doesn't play by the same rules that you have to play by, and in fact its first parameter is a boolean expression.

Upvotes: 1

Related Questions