Tuesday, May 22, 2012

Stored Procedure vs Inline Query, SQL


A very basic post but still worth mentioning. Here are the major differences between the two:

1. Stored procedures are stored in a pre-complied form.That is once a Stored procedure is executed, the compiled code is used in subsequent calls. This is not possible with inline queries.

2. Stored procedures reduces network traffic.
Since Stored procedures are stored in the server, only the name of Stored procedure is required to pass to the server. But in the case of inline queries, the complete query has to be passed to the server. So inline queries will increase network traffic when the queries are very large.

3. Stored procedures support Deferred Name Resolution.That is we can create stored procedures for objects(eg:- tables) which are not yet created (and will be creating in the near future)

4. Stored procedures prevents SQL Injection Errors.

5. By using Stored procedures we can separate all the queries from the Business logic code. Therefore we can create a separate layer. But while writing inline queries, all the queries have to be written (mixed up) with the business logic code which in turns causes issues while debugging.

6.Developers and database designers can work simultaneously using stored procedures. While a programmer writes business logic, another one can create stored procedures at the same time. :)

1 comment:

  1. Nice Article !

    Really this will help to people of Database Community.
    I have also prepared small note on this, Should we use stored procedure or Should we use inline query or ad-hoc query.

    http://www.dbrnd.com/2015/12/database-design-use-stored-procedure-do-not-use-inline-or-ad-hoc-sql/

    ReplyDelete