title: Are there any drawbacks ( things to care ) when using dynamic queries in stored procedures?
body: <p>I am developing website using asp.net.</p>
  
  <p>I have a situation Where I need to query a table based on several filter mechanisms driven by check-boxes in interface using SQL. So there may be several combinations for filters. So Finally I figured out that this can be achieve by using dynamic sql queries. So I wrote a stored procedure and then pass queries to stored procedure from the app after generating the right query.</p>
  
  <p>My sample stored procedure will be like this</p>
  
  <pre><code>CREATE PROCEDURE usp_dynamic(IN query_one VARCHAR(500))
  BEGIN
      SET @s = CONCAT('SELECT col1,col2 FROM test_table where',query_one );
      PREPARE stmt FROM @s;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  END
  </code></pre>
  
  <p>So I have few questions</p>
  
  <p>1) Is this safe? I mean will it lead to sql injections?</p>
  
  <p>2) Stored procedure are always compiled to best query plan. So dynamic queries will lead to recompile the SP when each time it got a query right?</p>
  
  <p>3) IS there any performance issues?</p>
  
  <p>4) Is there any other facts should I need to care when I use this approach?</p>
  
  <p>5) <strong>If there any issues what actions can be take to minimize those things?</strong></p>