Use where clq sql server

Question

Use where clq sql server

what i'm trying to do is make the request consider or not the datetime value, so i shouldn't be doing 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 something like

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

In one request

+5

sql sql-server case

user2085104 Feb 19 13 at 12:28 am

source to share

3 answers

John Woo · Answer 1 · 2013-02-19T00:30:01+0000

you just need to add a keyword END

SELECT * 
FROM    tableName
WHERE   dateCol =   CASE WHEN @considerDate =1
                         THEN @date 
                         ELSE dateCol 
                    END

sgeddes · Answer 2 · 2013-02-19T00:29:49+0000

If I understand you correctly, this should work:

SELECT * 
FROM Table
WHERE dateCol = 
   CASE WHEN @date is not null then @date ELSE dateCol END

mattyc · Answer 3 · 2013-10-16T22:28:12+0000

if @considerDate =0
    begin
        -- set the variable to null so the select will return all rows from table
        set @date = null
    end

SELECT *
FROM table
WHERE dateCol = IsNull(@date, dateCol)

If you don't want to include the date parameter in your query, set it to null, and in your query you can use a null check to decide if the filter is applied.

Use where clq sql server

More articles: