Conditional Operator in SQL Where Clause

Question

I'm wishing I could do something like the following in SQl Server 2005 (which I know isnt valid) for my where clause. Sometimes @teamID (passed into a stored procedure) will be the value of an existing teamID, otherwise it will always be zero and I want all rows from the Team table.

I researched using Case and the operator needs to come before or after the entire statement which prevents me from having a different operator based on the value of @teamid. Any suggestions other than duplicating my select statements.

    declare @teamid int
    set @teamid = 0

    Select Team.teamID From Team
      case @teamid
         when 0 then 
            WHERE Team.teamID > 0
         else
            WHERE Team.teamID = @teamid
      end

Andomar · Accepted Answer · 2010-04-02 19:38:58Z

22

You can do that without a case:

SELECT  Team.teamID 
FROM    Team
WHERE   (@teamid = 0 AND Team.teamID > 0)
        OR (@teamid <> 0 AND Team.teamID = @teamid)

answered Apr 2, 2010 at 19:38

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2010-04-02 19:42:12Z

5

Without using dynamic SQL, the most performant option is:

IF @teamid = 0
  BEGIN

    SELECT t.teamid
      FROM TEAM t
     WHERE t.teamid > 0

  END
ELSE
  BEGIN

    SELECT t.teamid
      FROM TEAM t
     WHERE t.teamid = @teamid

  END

Using Dynamic SQL:

DECLARE @SQL NVARCHAR(4000)
   SET @SQL = 'SELECT t.teamid
                 FROM TEAM t
                WHERE 1 = 1 '

   SET @SQL = @SQL + CASE @teamid
                       WHEN 0 THEN ' AND t.teamid > 0 '
                       ELSE ' AND t.teamid = @teamid '
                     END

BEGIN

  EXEC sp_EXECUTESQL @SQL N'@teamid INT', @teamid

END

Beware that sp_EXECUTESQL caches the query plan, while EXEC will not. Read this: http://www.sommarskog.se/dynamic_sql.html

answered Apr 2, 2010 at 19:42

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

3 Comments

Andomar Over a year ago

Wouldn't t think the added complexity of dynamic SQL or two IF blocks is worth it, unless you're querying the National Sport Association of China database ;-)

tpdi Over a year ago

But is the performance gain worth the complexity and maintainance cost loss?

OMG Ponies Over a year ago

All three options have their pros & cons - miss a bracket in Andomar's suggestion, and things won't work as intended.

Program.X · Accepted Answer · 2010-04-02 19:39:49Z

2

What about:

Select Team.teamID From Team Where (@teamid=0 and team.teamID>0) or (@teamid<>0 and team.teamid=@teamid)

answered Apr 2, 2010 at 19:39

Program.X

7,41212 gold badges53 silver badges86 bronze badges

Comments

tpdi · Accepted Answer · 2010-04-02 20:43:09Z

2

Even simpler than Andomar's answer, and assuming that id is never 0 (as for most auto-increment ids) is

SELECT  Team.teamID 
FROM    Team
WHERE   @teamid = 0 or Team.teamID = @teamid;

That predicate is always true when @teamid is zero, and otherwise only true when it matches a particular row's teamId.

Note however: this works pretty efficiently on Sybase 11 or above; it worked pretty inefficiently on MS SQL server 2003; I don't know how it works on the current version of MS SQL Server.

Also, you can use case; you just have to put the case in where clause, not the where clause in the case. So your original query would be:

Select Team.teamID 
From Team
where 
   case when @teamid = 0 then 0 else Team.teamID end = @teamId;

Note that this is likely to be less efficient, however, as it must be evaluated per row and will also likely result in a full table scan.

The form I gave above is more likely to be rewritten by a smart query optimizer (your mileage depends on your RDBMS) to use an index when @teamid is not zero.

answered Apr 2, 2010 at 20:43

tpdi

35.3k11 gold badges83 silver badges123 bronze badges

3 Comments

Andomar Over a year ago

+1 Your first answer is awesome. Even simpler might be @teamid in (0, TeamID) !

tpdi Over a year ago

Thanks! But using an inlist will probably not result in optimization.

Andomar Over a year ago

An in list is just syntactic sugar. I think a in (b,c) is expanded to a=b or a=c before the optimizer gets to see it

JeffO · Accepted Answer · 2010-04-02 20:30:20Z

0

If you could treat Null as all records:

WHERE Team.teamID = ISNULL(@teamid, Team.teamID)

answered Apr 2, 2010 at 20:30

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Collectives™ on Stack Overflow

Conditional Operator in SQL Where Clause

5 Answers 5

Comments

3 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related