CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Ananth
Ananth

Reputation: 10700

Why is this ORDER BY Clause in SQL Statement not working

I ran into an old code in an application. ORDER BY is not working here. From the execution plan, it looks like the ORDER BY is not executed at all.

IF(1 = 1)
(
SELECT * FROM dbo.Table WHERE   Column1= 'abc' 
)
ELSE
(
SELECT * FROM dbo.Table 
)



ORDER BY Column2

I know I can get it to work by refactoring this query like this. But I am just curious why the ORDER BY is not getting executed in the above query at the first place.

IF(1 = 1)
BEGIN
SELECT * FROM dbo.Table WHERE Column1= 'abc'  ORDER BY Column2
END
ELSE
BEGIN
SELECT * FROM dbo.Table ORDER BY Column2
END

Upvotes: 0

Views: 121

Answers (2)

jarlh
jarlh

Reputation: 44696

You can do a UNION ALL instead of the IF:

SELECT * FROM dbo.Table WHERE 1 = 1 AND Column1= 'abc' 
UNION ALL
SELECT * FROM dbo.Table WHERE NOT (1 = 1)
ORDER BY Column2

(Not here, but IF condition null values might need to be handled too.)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The ORDER BY is working, but only for the ELSE clause. SQL Server allows this syntax:

(SELECT * FROM dbo.Table )
ORDER BY Column2

And that is how the code is being interpreted. The ORDER BY is part of the ELSE. And I should point out that the IF condition is true, so it is the THEN query that is being executed.

Upvotes: 2

Related Questions