Transact-SQL Stored Procedure And Advantages

  • Posted on : June 16, 2009
  • Modified: November 5, 2020

  • Author : SPEC INDIA
  • Category : Big Data & Database

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
Author: SPEC INDIA


less words, more information

Tech
IN 200
words

Read our microblogs

Subscribe Now For Fresh Content

Loading

Guest Contribution

We are looking for industry experts to contribute to our blog section through fresh and innovative content.

Write For Us

Our Portfolio

Proven Solutions Across Industries
Technology for Real-Life

Visit Our Portfolio

Scroll Up