Stored Procedures  «Prev  Next»

Lesson 2What is a stored procedure?
Objective Describe what a stored procedure is and how it can be used.

What is a Stored Procedure?

A stored procedure is one or more Transact-SQL statements that are compiled and stored within SQL Server for very fast execution. If you are familiar with programming languages, you can think of a stored procedure as a function. The two are very similar, except that stored procedures are called from within another function, subroutine, or process.

Encapsulating code

A stored procedure is said to encapsulate functionality because you do not necessarily need to know how the stored procedure is coded. All you need to know is what the stored procedure does and the arguments that it expects.

Compiled code

Why does a stored procedure execute faster than other Transact-SQL statements?
SQL Server compresses and optimizes all the Transact-SQL code within a stored procedure. Then the stored procedure, along with its execution plan , is stored for future use. The execution plan, also known as a query plan, is SQL Server’s predetermined plan for which index(es) it will use, as well as the internal path that SQL Server will take to execute the Transact-SQL code. The execution plan is generated the first time the stored procedure is executed, rather than when the stored procedure is compiled.

Stored procedures can call other stored procedures, which can help make your applications very modular. Modular applications are beneficial because each module performs a single distinct unit of work, allowing other applications, or even parts of applications, to use only those units of work that are necessary.

Advantages of Stored Procedures

Stored procedures are often favored over dynamic Transact-SQL code (which is sent to SQL server at runtime) because of the following reasons:
  1. They are faster because the query plan is predetermined.
  2. They encapsulate very complex procedures.
  3. They offer a way to reuse code.
  4. They segregate development tasks for multiple developers.

They allow for easy security administering.

Types of stored procedures

There are three stored procedure types:
  1. User-defined stored procedures automate common Transact-SQL transactions and are created and used by the SQL programmer.
  2. System stored procedures provide important system maintenance functionality and are preinstalled with SQL Server 2012.
  3. Extended stored procedures reside in an external Dynamic Link Library (DLL) and allow you to access functionality that does not natively exist in SQL Server 2000.
In the next lesson, you will learn how to enforce business rules with stored procedures.

SQL-Server 2019