CODEWITHSUNDEEP
sqlsql-servercaseisnull
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Using ISNULL() on case expression result

I have a large case expression

e.g.

CASE WHEN ... THEN ... ELSE
CASE WHEN ... THEN ... ELSE '' END ELSE '' END +
CASE WHEN ... THEN ... ELSE
CASE WHEN ... THEN ... ELSE '' END ELSE '' END as [Test]

Now the [Test] column returns some NULLS. How can i wrap this CASE expression with (ISNULL, '') so that if it is NULL it is just blank?

What is the cleanest way to accomplish this with a very large case statement?

Upvotes: 1

Views: 6357

Answers (4)

Alays
Alays

Reputation: 140

I think the best way is to use a function for large case statement, this will be more readable for you. And easier to implement your solutions.

Upvotes: 0

AakashM
AakashM

Reputation: 63338

I may be missing the point but is there something you don't like about

ISNULL(
    CASE WHEN ... THEN ... ELSE
    CASE WHEN ... THEN ... ELSE '' END ELSE '' END +
    CASE WHEN ... THEN ... ELSE
    CASE WHEN ... THEN ... ELSE '' END ELSE '' END 
    , '')
AS [Test]

?

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would do :

SELECT ISNULL([Test], '')
FROM table t CROSS APPLY
     ( VALUES (CASE WHEN ... THEN ... ELSE
               CASE WHEN ... THEN ... ELSE '' END ELSE '' END +
               CASE WHEN ... THEN ... ELSE
               CASE WHEN ... THEN ... ELSE '' END ELSE '' END)
     ) tt ([Test]);

However, you can also apply ISNULL() to entire huge case expression :

SELECT ISNULL((CASE WHEN ... THEN ... ELSE
               CASE WHEN ... THEN ... ELSE '' END ELSE '' END +
               CASE WHEN ... THEN ... ELSE
               CASE WHEN ... THEN ... ELSE '' END ELSE '' END), ''
              )
. . .

The only advantage of APPLY is that you can use [Test] column anywhere or more than one time.

Upvotes: 1

Thom A
Thom A

Reputation: 95561

Just wrap the entire expression with an ISNULL:

ISNULL(
CASE WHEN ... THEN ... ELSE
CASE WHEN ... THEN ... ELSE '' END ELSE '' END +
CASE WHEN ... THEN ... ELSE
CASE WHEN ... THEN ... ELSE '' END ELSE '' END,'') as [Test]

Alternatively, you could use CONCAT, however, this would change the logic of your query slightly. If one expression didn't return a NULL and the other did, you would now get a value. NULL + {Non NULL Expression} = NULL however, CONCAT(NULL,{Non NULL Expression}) = {Non NULL Expression}.:

CONCAT(
CASE WHEN ... THEN ... ELSE
CASE WHEN ... THEN ... ELSE '' END ELSE '' END,
CASE WHEN ... THEN ... ELSE
CASE WHEN ... THEN ... ELSE '' END ELSE '' END) as [Test]

CONCAT will never return NULL. If every expression has the value NULL, an empty string ((n)varchar) is returned.

Upvotes: 1

Related Questions