How to skip where clause without If else in SQL query dynamically?-CodePudding

I'm attempting to write a query but the WHERE clause needs to be dynamically applied if search data exists, else skip that part in a single query.

Declare @search_text varchar(50)='', @start_date datetime, @end_date datetime;

If(@search_text = '')
begin
   select * from Table1 where 
   CONVERT(VARCHAR(12), Mydate, 101) BETWEEN CONVERT(Date, @start_date) AND 
   CONVERT(Date,@end_date)
end
else 
 begin 
   select * from Table1 where column1 like '%' @search_text '%'
 end

I am trying this but it is not working:


select * from Table1 where  ( Case when ISNULL(@search_text,'') = '' Then CONVERT(VARCHAR(12), MyDate, 101) END   
BETWEEN CONVERT(Date, @start_date) AND CONVERT(Date,@end_date) )

CodePudding user response：

You may combine the logic in a single query, with a single WHERE clause:

SELECT *
FROM Table1
WHERE
    @search_text = '' AND CONVERT(VARCHAR(12), Mydate, 101)
        BETWEEN CONVERT(Date, @start_date) AND CONVERT(Date, @end_date) OR
    @search_text <> '' AND column1 LIKE '%'   @search_text   '%';