Solution 1 is fine, but a little over-engineered. Just change your existing query to use a
CASE
statement
select resto.RestaurantName,resto.FoodPrepareTime,ram.OpeningTime,ram.ClosingTime ,
CASE WHEN @CurrentTime >= ram.OpeningTime AND @CurrentTime <= ram.ClosingTime THEN 'Open' ELSE 'Closed' ENDFROM #Restaurant Resto
join #RestaurantAvailablityMaster RAM on resto.RestaurantId = ram.RestaurantId
where RAM.DayId = @DayNumber
There is absolutely no need to use the
CONVERT
when handling the times - you are changing a time object to a varchar object which only serves to slow down your query. In fact you could do away with the variables altogether...
select resto.RestaurantName,resto.FoodPrepareTime,ram.OpeningTime,ram.ClosingTime ,
CASE WHEN CAST(getdate() AS TIME) >= ram.OpeningTime AND CAST(getdate() AS TIME) <= ram.ClosingTime THEN 'Open' ELSE 'Closed' END
FROM #Restaurant Resto
join #RestaurantAvailablityMaster RAM on resto.RestaurantId = ram.RestaurantId
where RAM.DayId = datepart(dw,getdate())