Conditional statement in SQL Where Where

Question

I want me to be able to do something like the following in SQl Server 2005 (which I know is not valid) for the where clause. Sometimes @teamID (passed to the stored procedure) will be the value of the existing command identifier, otherwise it will always be zero, and I want all the rows from the Team table.

I researched the use of Case, and the statement should come before or after the whole statement, which prevents me from having another statement based on the @teamid value. 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 

Answer 1

You can do this without incident:

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

Answer 2

Without using dynamic SQL, the most efficient 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, but EXEC does not. Read this: http://www.sommarskog.se/dynamic_sql.html

Answer 3

What about:

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

Answer 4

Even simpler than the Andomar query, and assuming the id is never 0 (as with most auto-increment identifiers),

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

This predicate is always true when @teamid is zero, otherwise it is only true when it matches a specific teamId command.

Please note: this works quite efficiently on Sybase 11 or higher; It worked quite inefficiently on MS SQL Server 2003; I do not know how this works in the current version of MS SQL Server.

Alternatively, you can use case; you just need to put the matter in the where clause, and not in the where clause. So your original request would look like this:

 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, since it should be evaluated on each row, and is also likely to lead to a full table scan.

The form above is more likely to be rewritten by the intelligent query optimizer (your mileage depends on your RDBMS) to use the index when @teamid is non-zero.

Answer 5

If you can treat Null as all entries:

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