LearnNowOnline

Blog

Introduction to Stored Procedures

Excerpt by Don Kiely | August 14, 2013

A stored procedure is a batch of Transact-SQL (T-SQL) code that is saved internally in SQL Server as a database object. You can use stored procedures to mask complex SQL tasks from clients, improve the performance of your application, enforce data and business rules, increase multi-user concurrency, and reduce locking and blocking conflicts. It is a module of T-SQL code that can perform just about any task in SQL Server, accessing data as well as resources outside of SQL Server. A stored procedure is the workhorse of SQL coding, and in this chapter you'll learn about how to use this important database feature.

Stored Procedure Features

Unlike scripts, which are saved outside your database as text files, stored procedures are saved as objects inside your database. You can execute a stored procedure from a client application, a batch or script, or from other stored procedures. Stored procedures are parsed and syntax-checked when you create them and are compiled on first execution. SQL Server caches the execution plan and shares it among all users of the database. Subsequent calls to the stored procedure can reuse the cached plan, even if different users call the stored procedure. This compilation, caching, and sharing of execution plans gives stored procedures a significant performance advantage over running ad hoc T-SQL statements.

You can also lock down the security of your database by revoking permissions on database objects and only granting access to data and objects through a stored procedure. Unlike views, which are also saved as database objects, stored procedures support the full capabilities of Transact-SQL. A single stored procedure can contain up to 250 megabytes of text, a limit you won't encounter anytime soon (or, if you do, you should seriously consider refactoring your code!) Stored procedures can accept input parameters and can return more than one result set, and they can also return data in the form of output parameters and a return value. Stored procedures are as close as you can come to functions or methods that you'll find in full-featured, object-oriented programming languages.

TIP: SQL Server uses stored procedures in most of its internal operations. System stored procedures have a schema name of sys and an sp_ prefix. You can read the Transact-SQL definitions of these stored procedures by running the sp_helptext system stored procedure and supplying the stored procedure name(for example, sp_helptext 'sys.sp_tables'). You'll find that most contain explanatory comments as well. Exploring system stored procedures is a great way to learn advanced T-SQL programming techniques once you've mastered the basics. The only tasks that you cannot complete with a stored procedure are the creation of triggers, defaults, rules, other stored procedures, and views.

A stored procedure can do everything from executing a basic SELECT statement to enforcing complex business logic and explicit transactions, as well as modifying data and other database and system objects.  

This post is an excerpt from the online courseware for our SQL Server 2012: Stored Procedures course written by expert Don Kiely.