SQL 2005+SQL Server Stored Procedures
The twenty-third part of the SQL Server Programming Fundamentals tutorial describes stored procedures. A stored procedure allows you to create a script, or batch, containing multiple commands to be executed, potentially being controlled by parameters.
What is a Stored Procedure?
A SQL Server stored procedure is a stored script or batch containing one or more Transact-SQL (T-SQL) statements that can be executed with a single command. The script can include any T-SQL statements, including those that query the database or create, modify or delete data in tables, as well as flow-of-control commands, mathematical operators and many predefined functions.
Stored procedures are often used to provide a level of abstraction for a database, hiding implementation details. Rather than permitting users from reading or updating information directly, you can create stored procedures that carefully control these operations and ensure that business rules are not broken. For example, in a banking system you may need to perform several updates to transfer money between accounts, ensuring that one account is credited, a second is debited and a third table is updated in a single transaction to record the transfer correctly and safely. By creating a stored procedure that includes all of these commands, a transfer can be executed in a single named call and in a consistent manner.
In the account transfer example, the transfer requires the knowledge of information such as a source and destination account and the amount of money to be moved. To allow such information to be used by a stored procedure, you can define parameters that accept values of any of the SQL Server data types. These are known as input parameters, as they are passed into the stored procedure and can be used freely within. The use of stored procedure parameters can be helpful when trying to avoid SQL injection attacks.
Stored procedures also permit the use of output parameters so that information can be returned to the caller of the batch. These allow information to be returned from the stored procedure in addition to the single return value that usually reports the status of the procedure. Of course, you can also query tables from within a stored procedure and return the results of the query as a recordset.
Some developers prefer not to use stored procedures in their applications. There are many reasons for this, one important one being the introduction of object-relational mapping (ORM) frameworks that use dynamically generate SQL statements to convert between data in a database and objects in an object-oriented programming language. There are many advantages to such an approach and a great deal of flexibility but also some disadvantages. Key to the disadvantages is that ORM frameworks generally require direct access to the database tables. This removes a level of abstraction and also limits security options. With stored procedures, you can restrict direct table access and force the used of the procedures, ensuring business rules are not overridden. Secondly, using a stored procedure only requires the sending of the name and parameters to the server. If a client performs complex functionality that requires many hundreds or thousands of statements to be executed, each of these may be sent over the network, generating unnecessary traffic. Some ORM tools recognise this and can call stored procedures, combining the use of the two technologies.
In this article we will create some stored procedures and examine the use of parameters. The examples will be reasonably simple and concentrate on the overall structure of a script rather than the fine detail. Over the rest of the tutorial we will be examining many more T-SQL commands including operators, functions, loops and conditional processing statements. Each of these can be used within stored procedures to create powerful scripts.
Creating Stored Procedures
In this article we will examine the creation of stored procedures against the JoBS tutorial database. This is the database that has been created throughout the course of the tutorial. If you do not have an up to date version of the database, you can use the link at the top of this article to download a script. Execute the script to generate the database and populate it with sample data.
The Stored Procedure Template
Stored procedures are created using the T-SQL "CREATE PROCEDURE" command. This command is used to specify the name of the new batch, any parameters that are required and the code of the procedure itself. Although the command can be typed manually into a query window, SQL Server Management Studio (SSMS) provides a template to get you started. To display the template, open SSMS and expand the JoBS database in the Object Explorer. You should see a node in the tree named "Programmability". Expand this branch of the tree to see the available elements for programming SQL Server. In this case, right-click the Stored Procedures section and select "New Stored Procedure..." from the menu that appears. A new query window containing the skeleton of a stored procedure is opened. The template will be similar to that shown below (though may vary depending upon the specific version of SQL Server that you are using):
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
The template includes various items that are useful when creating a stored procedure. Some of the key elements are described in the following sections.
15 March 2009