Subquery in ISNULL,IIF,CASE statements

Question

Subquery that is give as a parameter in ISNULL or IIF or CASE gets executed irrespective of the condition

To explain what I mean, consider the below example. When I run the below query and look in the execution plan, I find that even if the variable @Id not NULL, the second condition gets executed always.

Can anyone explain Why does the subquery gets executed in Query 1,2,3?

--Create a Temp table
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
CREATE TABLE #TempTable
(
ID INT
)

--Insert some data
INSERT INTO #TempTable VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

DECLARE @Id INT = 1

--Query 1: ISNULL
SET @Id= ISNULL(@Id, (SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1))
SELECT @Id AS ID

--Query 2: IIF
SET @Id=  IIF(@Id IS NULL,(SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1),@Id)
SET @Id=  IIF(@Id IS NOT NULL,@Id,(SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1))

SELECT @Id AS ID

--Query 3: CASE
SET @Id=  CASE WHEN @Id IS NULL THEN (SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1) ELSE @Id END
SET @Id=  CASE WHEN @Id IS NOT NULL THEN @Id ELSE (SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1)  END
SELECT @Id AS ID

---Query 4: IF
IF @Id IS NULL
BEGIN
    SET @Id = (SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1) 
    SELECT @Id AS ID
END
ELSE 
BEGIN
    SELECT @Id AS ID
END

Your queries are full of logical errors here. You have top everywhere but not a single order by. Unless you specify an order you have no way to ensure which row will be returned...of course in this case it can only be one row because of the where clause. And yes the execution plan has to determine how it might run. The plan is not conditional upon the data, it has to make the plan for any condition. — Sean Lange, Aug 21 '15 at 14:39
Simple answer would be SQL Server does not do Short-circuiting like other programming languages (c#,vb) etc. — M.Ali, Aug 21 '15 at 14:39
@SeanLange this is just a very small example I came up with..and of course there is a WHERE clause. I think the names are misleading, many might not expect this behavior, I guess most people wont even look into the query plan for just simple query as this, I just noticed this luckilly. — mkr, Aug 21 '15 at 14:53

score -1 · Answer 1 · answered Aug 21 '15 at 15:35

If you want the subquery to execute only when the null condition is met, use an IF instead of ISNULL

if @Id is null
   set @id = (SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1)

instead of

SET @Id= ISNULL(@Id, (SELECT TOP 1 ID FROM #TempTable TT WHERE TT.ID = 1))

Subquery in ISNULL,IIF,CASE statements

1 Answers1