Menu
I have a SP that gives me a lot of hard times.
The sp gets a two parameters @madeByUserId and @reportedByUserId. I want to have something like:
select * from table
where MadeByUserId = @madeByUserId (if(@reportedByUserID != null) and ReportedByUserID = @reportedByUserID)
Basically I want to make a case in the where clause to include another filter condition based of the null/not null state of the @reportedByUserId
Is that possible?
Thanks a lot, Radu
664
Null values can be used as a condition in the WHERE and HAVING clauses. For example, a WHERE clause can specify a column that, for some rows, contains a null value. A basic comparison predicate using a column that contains null values does not select a row that has a null value for the column.
Let's look at how to use the IS NULL condition in a SELECT statement in SQL Server. For example: SELECT * FROM employees WHERE last_name IS NULL; This SQL Server IS NULL example will return all records from the employees table where the last_name contains a null value.
You cannot use simple case to test for null because it always uses the equals operator ( = ). That is because the condition null = null is not true5—consequently, a when null clause never applies. If the <common operand> is null , the else clause applies.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
You could use COALESCE.
SELECT *
FROM Table
WHERE MadeByUserId = @madeByUserId
AND ReportedByUserID = COALESCE(@reportedByUserID, ReportedByUserID)
This translates to
if @reportedByUserID is `NOT NULL` then
compare ReportedByUserID with @reportedByUserID
else
compare ReportedByUserID with ReportedByUserID
From MSDN
COALESCE
Returns the first nonnull expression among its arguments.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
