A stored procedure is a database object that contains one or more SQL statements. In the topics that follow, you’ll learn how to create and use stored procedures. In addition, you’ll learn how to use some of the stored procedures provided by SQL Server.
Statement below presents a script that creates a stored procedure, also called an sproc or just a procedure. To do that, you use the CREATE PROC statement. You’ll learn the details of coding this statement in a moment.
The first time a procedure is executed, each SQL statement it contains is compiled and executed to create an execution plan. Then, the procedure is stored in compiled form within the database. For each subsequent execution, the SQL statements are executed without compilation, because they’re precompiled. This makes the execution of a stored procedure faster than the execution of an equivalent SQL script.
To execute, or call, a stored procedure, you use the EXEC statement. If the EXEC statement is the first line in a batch, you can omit the EXEC keyword and just code the procedure name. Since this can lead to code that’s confusing to read, however, I recommend that you include the EXEC keyword.
The script in this section creates a stored procedure named spInvoiceReport. This procedure consists of a single statement: a SELECT statement that retrieves data from the Vendors and Invoices tables. As you’ll see in the topics that follow, however, a stored procedure can contain more than one statement, along with the same procedural code used in scripts.
When you execute the script in this section, you create the stored procedure. The response from the system shows that the procedure was created success-fully. Then, when you execute the stored procedure, the result set retrieved by the SELECT statement is returned.
As you can see, a user or program that calls this procedure doesn’t need to know the structure of the database to use the stored procedure. This simplifies the use of the database by eliminating the need to know SQL and the need to understand the structure of the database.
As you’ll learn in chapter “How to manage database security”, you can allow a user or program to call specific stored procedures but not to execute other SQL statements. By doing this, you can secure your database by restricting access to only those rows, columns, and tables that you provide access to through the stored procedures. For those systems where security is critical, this can be the best way to secure the data.
A script that creates a stored procedure
CREATE PROC spInvoiceReport
SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
ORDER BY VendorName;
The response from the system
Command(s) completed successfully.
A statement that calls the procedure
The result set created by the procedure
- A stored procedure is an executable database object that contains SQL statements. A stored procedure is also called a sproc (pronounced either as one word or as “ess-proc”) or a procedure.
- Stored procedures are precompiled. That means that the execution plan for the SQL code is compiled the first time the procedure is executed and is then saved in its compiled form. For this reason, stored procedures execute faster than an equivalent SQL script.
- You use the EXEC statement to run, or call, a procedure. If this statement is the first line in a batch, you can omit the EXEC keyword and code just the procedure name. To make your code easier to read, however, you should always include the EXEC keyword.
- You can call a stored procedure from within another stored procedure. You can even call a stored procedure from within itself. This technique, called a recursive call or recursion, is seldom used in SQL programming.
- One of the advantages of using procedures is that application programmers and end users don’t need to know the structure of the database or how to code SQL.
- Another advantage of using procedures is that they can restrict and control access to a database. If you use procedures in this way, you can prevent both accidental errors and malicious damage.