Calling a View from Procedure 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.
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 !!!