How to use condition in T-SQL

if condition in sql server
case when sql
sql nested if statement in select
sql server stored procedure if statement multiple conditions
sql if else begin end
sql iif
sql condition
if else in sql stored procedure

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?


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.

IFELSE (Transact-SQL), The SQL Server (Transact-SQL) IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE  In a nutshell, the condition is whether Case_Expression = Value_N and ACTION is the execution of Statement_N if the above result is TRUE. ALIAS_NAME is optional and is the alias name given to CASE statement result. Mostly used when we use CASE in the select clause. Rules for Simple Case:


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

SQL Server IF ELSE Statement By Examples, This SQL Server tutorial explains how to use the AND condition and the OR condition together in a SQL Server (Transact-SQL) query with syntax and examples. Arguments < search_condition > Defines the condition to be met for the rows to be returned. There is no limit to the number of predicates that can be included in a search condition. For more information about search conditions and predicates, see Search Condition (Transact-SQL).


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

SQL Server: IN Condition, of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java and XML. MS Access Functions The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-​THEN-ELSE statement). If no conditions are true, it returns the value in the ELSE clause. Following are Conditional Expressions in SQL. The CASE Expression: Let you use IF-THEN-ELSE statements without having to invoke procedures. In a simple CASE expression, the SQL searches for the first WHEN……THEN pair for which expr is equal to comparison_expr and returns return_expr. If above condition is not satisfied, an ELSE clause exists, the SQL returns else_expr.


SQL Server: Combining the AND and OR Conditions, To begin, we of initialize the CASE statement then specify under which conditions ( WHEN ) our CASE statement should evaluate a result. In this example, we're  The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)


SQL CASE Statement, Use IFELSE logic in a stored procedure to selectively execute a code block. If you're like most casual user of T-SQL then you're you've mostly likely IF the condition evaluates to true then the statement or statement block is executed. One method which is supported on all systems the use of a SELECT statement to return the row values, with the condition set in that SELECT statement: INSERT INTO <table> (<col1>, <col2>) SELECT <val1>, <val2>


How to Use IFTHEN Logic in SQL Server, In the following example, we use a variable in the Boolean expression to execute the statement based on the condition. For example, if a student  Using SQL CASE with ORDER BY. It is possible to use CASE with ORDER BY to order results based on if then else logic. Extending our last query to have an ORDER BY included, the criteria here is to ensure that the ‘Persian’ breed is the first row.