Transact-SQL Stored Procedure and Advantages

A Transact-SQL stored procedure is a set of T-SQL code that is stored in a SQL Server database and compiled when used. You create this set of code using the CREATE PROCEDURE command. You can use most Transact-SQL commands in a stored procedure; however, some commands (such as CREATE PROCEDURE, CREATE VIEW, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and so forth) must be the first (or only) statement in a command batch, and therefore aren’t allowed in stored procedures. Most Transact-SQL commands behave the same in a stored procedure as they do in a command batch, but some have special capabilities or exhibit different behavior when executed within the context of a stored procedure.

Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries as mentioned below.

  1. Execution plan retention and reuse
  2. Query autoparameterization
  3. Encapsulation of business rules and policies
  4. Application modularization
  5. Sharing of application logic between applications
  6. Access to database objects that is both secure and uniform
  7. Consistent, safe data modification
  8. Network bandwidth conservation
  9. Support for automatic execution at system start-up
(Visited 5 times, 1 visits today)


Related Posts:

210
Author - admin_specindia

POPULAR BLOGS

Archives

  • 2017
  • 2016
  • 2015
  • 2014
  • 2013
  • 2012
  • 2011
  • 2010
  • 2009
  • 2008
  • Current Openings
    Scroll Up