Benefit of stored procedure over inline query

Stored procedure has been a standard in Titansoft for a long time. So why did we choose this over inline query? What are the benefits? Well, in my opinion, here are the top 4!

• Execution plan retention and reusability
  • Execution plan is a plan for the SQL server to retrieve data. It is generated whenever SQL server receives a query. The SQL server will try to find out the optimal way to execute the query. Therefore, if the execution plan is not generated each time a query comes in, it can save some execution time.
  • The SQL server will reuse execution plan of an Ad-hoc query if the subsequent query is exactly the same.

 select Name from customer with(nolock) where customerid = 1
 select Name from customer with(nolock) where customerid = 2

SQL server will not recognize these 2 queries as the same query and reuse the execution plan. However if the condition is replaced by a parameter like “customerid = @customerId”, SQL server is able to recognize it as same query.

• Reduce network traffic
  • Passing only name of stored procedure and parameters instead of whole content of query will reduce network traffic between application and database.

• Code reusability and better maintainability
  • Since stored procedure is saved on a database level, sharing of application logic between applications is easier than using libraries or APIs.
  • It is easier to troubleshoot a stored procedure than inline query as we can isolate it. There is also a way to write unit test in SQL server which help to confirm the logic of stored procedure.
  • Performance tuning is possible to do on stored procedure level. DBA/ developer can also recompile or terminate the stored procedure to solve performance issue quickly.

• Better security
  • SQL Injection attack can be avoided if query uses parameterized input. SQL injection is an attack to inject malicious SQL statement to be executed via invalidated input.
    Here is a example of query that vulnerable to SQL injection attack:

    string sql = "select * from customer with(nolock) where firstname = '" + FirstNameInput.Text + "'";

    If user input FirstNameInput.Text = “‘1’ or ‘1’=’1′”; The query will become:

    string sql = "select * from customer with(nolock) where firstname = '1' or '1'='1';"

    Through this way, attacker can bypass any condition to get unauthorized data. Stored procedure with parameterized input can prevent of SQL injection attacks.

  • Permission can be controlled on stored procedure level for specified database roles. The application does not need to have direct access to underlying tables. Then if security of application is being comprised, the manipulation of data will not be so easy.

Even though there are many advantages of stored procedure over inline query, they may not necessary hold for your application. If this is the case, there are other alternatives to consider, like Object Relation Mapping (ORM). All in all, it is still recommended to choose the implementation based on your needs!