Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

use case in where clause sql server

what im trying to do is to make a query consider or not a datetime value so i dont have to do this in a stored procedure

if @considerDate =1
    begin
        select * from table 
        where dateCol = @date
    end
else
    begin 
        select * from table
    end

i would like to do somethink like

select * from table
where   dateCol = ( case 
                    when  @date is not null
                    then @date
            )

In an single query

like image 807
pepe0217 Avatar asked Feb 19 '13 00:02

pepe0217


People also ask

Can I use case in WHERE clause SQL Server?

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.

Can we use case after WHERE clause in SQL?

Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.

Can we use WHERE in case when?

You can use case in a where , but not like that. Case has to return one value per statement.

Can we use and in WHERE clause in SQL?

The WHERE clause can be combined with AND , OR , and NOT operators.


1 Answers

you just need to add END keyword

SELECT * 
FROM    tableName
WHERE   dateCol =   CASE WHEN @considerDate =1
                         THEN @date 
                         ELSE dateCol 
                    END
like image 89
John Woo Avatar answered Sep 29 '22 23:09

John Woo