5

I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.

For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .

Query goes something like this inside procedure

set @cnt = (select count(*) from table where date_field between date1 and date2) ; 
if @cnt > 0 then 
    select * from table where date_field between date1 and date2 ; 
else 
    select * from table order by date_field desc limit 0,20 ; 
end if ;

Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.

5

You could probably reduce this to 2 queries with something like:

select * from table where date_field between date1 and date2 ; 
set @count = found_rows()
if @count = 0 then
    select * from table order by date_field desc limit 0,20 ; 
end if ;
|improve this answer|||||
  • It returns me 2 result sets, 1 is empty result set and another one for condition block. I don't want to get two result sets. That is why i have used variable to store the count. – Nikesh Kedlaya Aug 19 '17 at 4:49
  • @Nikesh; I've updated the answer with what I think will address your comment, namely, storing the result from found_rows() into a variable thus eliminating the undesired result set – markp-fuso Aug 19 '17 at 16:08
2

This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):

IF ( EXISTS( select * from table where date_field between date1 and date2 ) ; 
    select * from table where date_field between date1 and date2 ; 
else 
    select * from table order by date_field desc limit 0,20 ; 
end if ;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.