Clever Geek Handbook

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


source to share


3 answers


you just need to add a keyword END 



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


source


If I understand you correctly, this should work:



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


source 


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.

+3


source






More articles:


All Articles