CODEWITHSUNDEEP
sqlsql-serverselectwhere-clause
Limak
Limak

Reputation: 47

Select all rows by variable in WHERE clause in SQL Server

I have a procedure and sometimes I want to select rows with myBool = 0, sometimes with 1 and sometimes all the rows (so nevermind is it 0 or 1). How can I write an expression that does it but using local variable in WHERE clause? So to be constructed like this one:

SELECT * FROM table WHERE myBool = @myBool;

Upvotes: 0

Views: 2676

Answers (3)

Thom A
Thom A

Reputation: 95554

Considering the simplicity, I would suggest using the values 1, 0 and NULL for @MyBool, then you can do the below:

SELECT {Columns}
FROM dbo.YourTable
WHERE MyBool = @MyBool
   OR @MyBool IS NULL;

Note, however, that can result in some bad query plan caching, so I would recommend adding OPTION (RECOMPILE) to the query.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The canonical way would be to allow @myBool to take on NULL value:

WHERE myBool = @myBool OR @myBool IS NULL

I should note that this doesn't optimize well. But, if you have just two values, then an index is probably not going to be used anyway.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If you want both then don't need to pass 1 or 0 make it null :

where (myBool = @myBool or @myBool is null)

Upvotes: 1

Related Questions