CODEWITHSUNDEEP
sqlsql-serverperformanceshortcut
CodingBarfield
CodingBarfield

Reputation: 3398

Sql short circuit OR or conditional exists in where clause

I'm trying to force sql server to make a short circuit OR comparison on some fields. On the left side of the Or I have a simple variable comparison and on the right side I have a pretty 'heavy' subquery.

WHERE
(@Var = 'DefaultValue' )  OR 
Exists(select * from atable)

Is there any way to only execute the right side of the or statement if the first statement is false.

I've tried case and if else statements but can't find any working syntax.

I'm using a MS SQL server

Upvotes: 0

Views: 1037

Answers (2)

Vikram Sinha
Vikram Sinha

Reputation: 1

How about this?

WHERE 1 = CASE @Var
               WHEN 'DefaultValue' THEN 1
               ELSE (SELECT TOP 1 1 FROM atable)
          END

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

You can't do what you want in a single SQL statement. You can do something like this however in a stored proc

If @Var = 'DefaultValue' then
BEGIN
     SELECT * FROM table 
END
ELSE
BEGIN
     SELECT * FROM table
     WHERE Exists(select * from atable)
END

If you've got a lot of inputs you should consider Dynamic SQL

Upvotes: 3

Related Questions