How to use condition in T-SQL

Question

I'm trying to use a select case in order to either use a where condition or not in a sql query. But it does not work and I can use some help how to solve this if possible

DECLARE @CategoryID Int
SET @CategoryID = 0
SELECT * FROM SomeTable 
CASE WHEN @CategoryID = 0 THEN 
ELSE
WHERE        (dbo.SomeTable.ID = @CategoryID)
END

So if I pass 0 to the @CategoryID parameter I should not filter at all, otherwise I want to filter. Can this be done?

The [references docs](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15) show how to use the case expression. — Dale K, Oct 16 '19 at 06:31
You Can Use Dynamic Query. http://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/ — Amin Golmahalleh, Oct 16 '19 at 06:37

score 4 · Accepted Answer · answered Oct 16 '19 at 06:30

Rephrase your logic to remove the CASE expression:

SELECT *
FROM SomeTable
WHERE @CategoryID IN (0, ID);

This works, because when @CategoryID = 0, the WHERE clause is always true, otherwise it would be true when ID = @CategoryID.

Your current attempt is a common problem, stemming from not using a CASE expression as it is intended. The predicate of a CASE expression (i.e. what follows the logic after THEN and ELSE) has to be a literal value, not another logical expression. In this case, we don't even need CASE.

score 0 · Answer 2 · answered Oct 16 '19 at 06:42

Your syntax is wrong... You have to rearrange your where clause:

DECLARE @CategoryID Int = 0;

SELECT * FROM SomeTable 
WHERE @CategoryID = 0 OR @CategoryID = ID

If variable is set to 0, then where clause is always true, otherwise, its value depends on condition @CategoryID = ID

score 0 · Answer 3 · answered Oct 16 '19 at 06:42

This is possible, first is to build your query based on your case condition

DECLARE @CategoryID INT
SET @CategoryID = 0
declare @strsql varchar(max)

set @strsql =  
    CASE WHEN coalesce(@CategoryID, 0) = 0 THEN 'SELECT * FROM SomeTable'
    ELSE
        'SELECT * FROM SomeTable WHERE 1 = '+ cast(@CategoryID as varchar(10)) + ''
    END
print  @strsql
exec sp_executesql @strsql

How to use condition in T-SQL

3 Answers3