Santhos February 2016

CASE WHEN bit THEN 'something' ELSE null END shortcut

Is there a shortcut in MS SQL Server for a call CASE WHEN bit THEN 'something' ELSE null END ? Is there a way to write it differently?

Example scenario:

DECLARE @data TABLE(IsSomething BIT NOT NULL, Value NVARCHAR(30) NOT NULL);

INSERT INTO @data VALUES
(1, 'a'),
(1, 'b'),
(0, 'c'),
(0, 'd');

SELECT
CASE WHEN IsSomething = 1 THEN Value ELSE NULL END,
CASE WHEN IsSomething = 1 THEN 'string' ELSE NULL END,
CASE WHEN IsSomething = 0 THEN 'not ' + Value ELSE NULL END
FROM @data

I am especially looking for the variant where the BIT value is 1 (true).

The concern is readability of the select statement and excesively long expression to write for a simple case/if.

I use those expressions in one of my views where it bothers me the most. I am not saying the design of that view or the tables is ideal. (In fact, it is not ideal!)

Answers


Tab Alleman February 2016

Don't know what you would consider a shortcut, but you could write it this way:

CASE IsSomething
 WHEN 1 THEN 'SomeThing'
 ELSE NULL
END


Alex February 2016

You could use IIF (SQL Server 2012+) which is supposed to be a shorthand for CASE, but in my opinion the query ends up being even less readable (might be a matter of habit on my end though):

SELECT
    IIF(IsSomething = 1, Value1, NULL) AS val1,
    IIF(IsSomething = 1, Value2, NULL) AS val2,
    IIF(IsSomething = 0, Value3, NULL) AS val3
FROM @data

Post Status

Asked in February 2016
Viewed 3,343 times
Voted 14
Answered 2 times

Search




Leave an answer