Wednesday, October 31, 2012

SQL Server 2012 IIF function may replace CASE expression?


Hi
Did you install already SQL Server 2012? Well, see a new T-SQL function named IIF you can use sometimes instead of CASE expression.
http://msdn.microsoft.com/en-us/library/hh213574.aspx


CREATE TABLE #t (id INT)

INSERT INTO #t VALUES (1),(2),(3)

DECLARE @a int = 3;
DECLARE @b int = 2;

SELECT * FROM #t WHERE id= IIF ( @a > @b, @a, @b )

Returns Id=3.

You can even using nested IIF commands.
SELECT * FROM #t WHERE id= IIF ( @a > @b, IIF(@a>0,@b,0), @b )