1

I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters.

Ex:

 CASE pSearchType
  WHEN 1 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1 ;
  WHEN 2 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1 
      AND Col2 = pInput2;
  WHEN 3 THEN
    SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...
      WHERE Col1 = pInput1 
      ORDER BY Col4;
  END CASE;

So is it advisable to have:
1) The SQL Statement as a View and Call the View from procedure with the Where clause and Order By added? * Good for maintenance *

2) Or Have the SQL Statement inside each of the CASE statement?

Which one will be preferred way for performance?

PS: My procedure will have parameters that will have NULL (mean Optional) and those parameters will be used in where conditions.

Thanks.

  • What is pSearchType, a parameter? – ypercubeᵀᴹ Jul 22 '15 at 20:24
  • @ypercube Yes, it is a Parameter. Sorry I did not specified that. – Prabhat Jul 23 '15 at 4:33
2

How about Dynamic SQL ?

SET @query = 'SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...';
CASE pSearchType
  WHEN 1 THEN
    SET @where = 'Col1 = pInput1';
  WHEN 2 THEN
    SET @where = 'Col1 = pInput1 AND Col2 = pInput2';
  WHEN 3 THEN
    SET @where = 'Col1 = pInput1 ORDER BY Col4';
END CASE;
SET @sql = CONCAT(@query,' WHERE ',@where);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Please make sure you create indexes or any appropriate tuning that will support each case.

Give it a Try !!!

  • thank you for your input. Dynamic is good, but not sure how the query plan will be taken care in MySQL (InnoDB). Can you advise? Also can you advise how will MySQL engine treat if we call the View from the procedure as per the question. – Prabhat Jul 23 '15 at 4:35

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.