You can find stored procedures, under the Programmability
folder for each database in Object Explorer. Stored procedures are
routines saved in the database that contain business logic used to
perform predefined actions and return information about the action that
was performed. If you can write a set of code to run in a batch
statement, you can most likely encapsulate the code in a stored
procedure. A stored procedure can accept input parameters; perform a
number of actions using both DDL and Data Manipulation Language (DML)
statements, and return output to the client in the form of a scalar
value, one or more result sets, or output parameters. A stored procedure
can also return an execution status indicating the success or failure
of the procedure.
Stored procedures provide
many benefits when executing T-SQL including code reusability. You can
code logic in stored procedures and call the stored procedure from any
application that requires the same information. Stored procedures also
provide a layer of abstraction from the application. You can easily
change the code within the stored procedure without having to make any
changes to the application. You also reduce the network traffic that is
sent between the client and the database server. Instead of sending an
entire query or batch of code over the network, you will only need to
send a single line to execute the procedure. Stored procedures often
lead to a performance increase because the query optimizer compiles a
query plan for the stored procedure the first time it is executed, and
the query plan is then reused for subsequent executions. The reason I
say a compiled query plan often leads to increased performance is
because in some cases the query plan that the query optimizer chose when
the stored procedure was first executed may not be the best plan for
subsequent executions with different parameters and actually causes a
performance hit. Stored procedures enhance security by allowing you to
grant rights to execute the procedure instead of giving access directly
to the base tables. Also, by using parameterized queries, you reduce the
chances of a SQL injection attack. Keep in mind that just because you
use a stored procedure does not mean you are immune to a SQL injection
attack. Bad coding techniques, such as dynamic SQL, are prone to a SQL
injection no matter where the code is encapsulated.
When you create a stored procedure, you should not use the sp_
naming convention because this prefix is designated for SQL Server
system stored procedures. If you create a procedure with the sp_prefix, it may conflict with a current or future system stored procedure. Listing 1
shows how to create a simple stored procedure that accepts a group name
as a parameter and returns all the departments in that group.
Example 1. Syntax to Create and Execute a Stored Procedure That Accepts a Parameter and Returns a Result Set
USE AdventureWorks2008 GO
CREATE PROCEDURE dbo.DepartmentNameByGroup_Select (@GroupName nvarchar(50)) AS SELECT DepartmentID, Name AS DepartmentName FROM HumanResources.Department WHERE GroupName = @GroupName
GO --Execute the stored procedure EXEC dbo.DepartmentNameByGroup_Select 'Executive General and Administration'
|
You can see the results of Listing 1 in Figure 1. You would typically create a stored procedure like the one in Listing 1 to bind to a drop-down list in an application.
You can use an output
parameter to capture and return a single value to the code that calls
the stored procedure. However, you can have multiple output parameters
within a single stored procedure. The following example in Listing 2 alters the stored procedure that we created in Listing 1 to add an output parameter named @DepartmentCount that is used to hold the number of departments for the group being returned. (Notice the OUTPUT clause in the parameter definition.)
When you run a procedure with an output variable, you must define a variable in the calling code to hold the return value. In Listing 2, we have defined a variable called @NbrOfDepartments. That is the variable that receives the value from the procedure's output parameter.
You must also use the OUTPUT clause following the variable that will be used to capture the return value, or you will end up with a NULL variable in the calling code. So you must use the keyword OUTPUT in the parameter definition, as well as when actually passing the parameter to the procedure.
Example 2. Syntax to Alter the Stored Procedure Created in Listing 1 to Use an Output Parameter
USE AdventureWorks2008 GO ALTER PROCEDURE dbo.DepartmentNameByGroup_Select (@GroupName nvarchar(50), @DepartmentCount int OUTPUT) AS SELECT DepartmentID, Name AS DepartmentName FROM HumanResources.Department WHERE GroupName = @GroupName GROUP BY DepartmentID, Name;
SELECT @DepartmentCount=COUNT(1) FROM HumanResources.Department WHERE GroupName = @GroupName; GO
--Execute the stored procedure DECLARE @NbrOfDepartments int
EXEC dbo.DepartmentNameByGroup_Select 'Executive General and Administration', @NbrOfDepartments OUTPUT
SELECT 'There Are ' + CAST(@NbrOfDepartments as varchar(50)) + ' Departments In This Group.'
|
As you can see in Figure 2, the code from Listing 2
contains two result sets. The output parameter has no visual effect on
the first result set; you can only see the output returned from the
stored procedure by specifying the variable used to capture the output
in a second query.
You can also use the RETURN clause to return information to the code that calls a stored procedure. The RETURN clause is more limited than using output parameters in that you can only have a single RETURN clause per stored procedure that returns an integer value. The RETURN clause also immediately stops the execution of a stored procedure and returns control to the calling code.
Listing 3 alters the stored procedure created in Listing 1 to use the RETURN clause to return the number of departments for a group. Notice that now we are setting the @NbrOfDepartments
variable equal to the results of the stored procedure instead of using
it as a parameter as we did with the output parameters. The result set
is exactly the same as what was shown in Figure 2 .
Example 3. Syntax to Alter the Stored Procedure Created in Listing 1 to Use the RETURN Clause
USE AdventureWorks2008 GO ALTER PROCEDURE dbo.DepartmentNameByGroup_Select (@GroupName nvarchar(50)) AS
SELECT DepartmentID, Name AS DepartmentName FROM HumanResources.Department WHERE GroupName = @GroupName GROUP BY DepartmentID, Name;
RETURN (SELECT COUNT(1) FROM HumanResources.Department WHERE GroupName = @GroupName);
GO
--Execute the stored procedure DECLARE @NbrOfDepartments int
EXEC @NbrOfDepartments = dbo.DepartmentNameByGroup_Select 'Executive General and Administration'
SELECT 'There Are ' + CAST(@NbrOfDepartments as varchar(50)) + ' Departments In This Group.'
|
Because a stored procedure's
query plan is cached, you may find yourself needing to recompile a
stored procedure in order to benefit from recent changes to your
database, such as an added index. A stored procedure is automatically
recompiled the first time it is run after SQL Server is restarted as
well as when the structure of an underlying table is changed, but adding
an index does not force a recompile.
You can use the sp_recompile system stored procedure to force a stored procedure to produce a new query plan the next time it is executed (see Listing 4). The sp_recompile
procedure takes an object name as an input parameter. If you pass in
the name of a stored procedure, it will be recompiled the next time it
runs; if you pass in the name of a table, any stored procedure that runs
against that table will be recompiled.
Example 4. Syntax to Force the dbo.DepartmentNameByGroup_Select Stored Procedure to Generate a New Execution Plan
USE AdventureWorks2008 GO EXEC sp_recompile 'dbo.DepartmentNameByGroup_Select' GO
|
To remove a stored procedure from the database, you can issue the DROP PROCEDURE statement followed by the procedure name, as shown in Listing 5.
Example 5. Syntax to Drop a Stored Procedure from the Database
DROP PROCEDURE dbo.DepartmentNameByGroup_Select
|
You also have the option to execute a stored procedure automatically whenever SQL Server starts by using the sp_procoption
system stored procedure. In order to enable a stored procedure for auto
execution, it must exist in the master database and cannot contain
input or output parameters. The code in Listing 6
shows the syntax to enable a stored procedure for auto execution. In
order to disable auto execution on the stored procedure, change the @OptionValue to off.
Example 6. Syntax to Enable Stored Procedure Execution on SQL Server Startup
sp_procoption @ProcName = 'uspSystemMaintenance',
@OptionName = 'startup',
@OptionValue = 'on'