Stored Procedure Overview
Introduction
I chose Stored Procedures as a topic because, while Database Administration can be done without writing them, they are a key to automating many of the administration tasks. The intent is not to determine what can be automated or to cover the T-SQL language, but to cover some features and possible methodologies for stored procedures.
What are Stored Procedures?
A stored procedure is an object in a database that contains executable code. The code usually is T-SQL and can contain DDL and DML. There are also .NET Common Language Runtime, Extended Stored Procedures (DLLs), and System Stored Procedures, but those are beyond the scope of this paper.
There isn't much required for a simple stored procedure. Here is the format with optional values in brackets:
CREATE PROCEDURE (or PROC) procedure_name
[parameter declarations]
[WITH [RECOMPILE] [, ENCRYPTION] [, EXECUTE_AS_clause]]
AS
T-SQL statements
Calling Stored Procedures
Stored procedures can be called or ran from applications (such as a .net framework language like C# or VB), from a SQL script, or from other stored procedures.
The syntax is from a SQL script or other stored procedure is:
sp_Name (if it's the first line of a batch)
or
Execute sp_Name
or
EXEC sp_Name (the best practice is to always use EXEC)
Why use Stored Procedures?
In my research I found a several reasons to use stored procedures:
- Time is saved by executing automated tasks rather than doing the task manually.
- Automated tasks reduce the chance for errors since steps are done in the correct order and no steps are missed.
- For security reasons you can allow users to execute stored procedures and view the results instead of giving them access to SQL statements.
- Complex tasks that users or developers don't have the skills to code can be automated for them to give them the desired data.
Modifying and Deleting Stored Procedures
To modify an existing procedure the Alter statement is used. In SQL Server Management Studio you can right-click on a stored procedure and select Modify or “Script Stored Procedure as” then “Alter To” then “New query window”. That opens a SSMS window with the Alter Procedure statement.
Use the Drop procedureName statement to delete a stored procedure.
Comments on Comments
Comments are used to document and describe code.
-- This is a single line comment.
EXEC sp_Test -- This is a comment after a line of executable code.
/*
This is a
multi-line comment.
*/
They are often used at the beginning of a procedure. Typical information may include:
/*
Description of SP:
Parameter description:
Input parameters
Output parameters
Created by: Developer Name
Create on: Date
Updated on: Date
Update reason: This is the reason for the update.
*/
Statement Blocks
Any time lines of code need to be executed as a group, Begin and End statements can be used. For example, if more than one line needs to be executed conditionally with an IF statement, then surround the lines with Begin and End:
IF (boolean condition)
BEGIN
line of code
line of code
END
Parameters
Parameters are used to pass values to or from a procedure. Parameters are “input” by default meaning that a value is passed to the procedure for use within the procedure. An example might be a date that's used in a Select statement Where clause to limit records. “Output” parameters pass data back to the calling script or procedure and are indicated by the word output in the declaration.
Parameters must be named with an “@” prefix and be of a particular data type and may be defaulted to an initial value. Parameters are declared after the Create Procedure proc_name statement and before the “AS” statement. Multiple parameter declarations are separated by commas:
Create Procedure proc_name
@ParamName1 varchar(10) (parameter type) = NULL (initial value) ,
@ParamName2 integer (parameter type) output (returns a value)
As
Variable Declaration and Initialization
Variable Declaration and Initialization
Variables are similar to parameters in that they have an @ sign before their name and are assigned one of the SQL Server data types.
The “Declare” statement creates a variable. Values can be initialized and assigned with a value with the “Set” statement.
Example:
Declare @StartDate datetime
Set @StartDate = GetDate()
In SQL Server 2008 the declaration and initialization can be combined into one statement:
Declare @StartDate2 datetime = GetDate();
Returning Data to a Calling Application
There are several ways to return data directly to the calling application.
- The “Return” command generally returns a 0 (zero) if the stored procedure execution was successful and a 1 (or anything but a zero) if unsuccessful.
- The “Print” command is used to return a message as a string and can be concatenated with other variables, parameters or values.
- A Result Set can be returned simply by executing a SQL Select statement.
- Output parameters were touched on briefly in the parameters section. Corresponding variables with the same type should be set up in the calling application to capture the values passed back in the output parameters.
Error Handling
When run-time errors occur, instead of letting SQL Server display a possibly cryptic error message and ending execution, errors can be detected and more meaningful messages displayed to the user, or an error can be caught, logged and execution can be allowed to continue.
The “Raiserror” statement is used to stop execution of a procedure and display a message to the user.
Detecting an error can be as simple as using an IF statement to check for required values, such as with required parameters. In this case if a parameter is null, the Raiserror statement is used to raise an error and display a message. Further information is displayed with the Print statement and a value of 1 is returned with the Return statement to indicate that the stored procedure failed.
If @Year Is Null
Begin
raiserror 99999 'You must supply a year'
-- Display a hint on how to run the procedure.
Print 'Usage: Exec spRegionalYearlyProductSales region name,
product name, year, @TotalSales output,
@TotalQuantity output'
Return 1
End
BeginTry and EndTry statements are used to surround lines of code to be executed. If an error occurs between the BeginTry and EndTry, control is transferred to the statements between the BeginCatch and EndCatch statements. The error can be “handled” by using the “Raiserror” command.
Begin Try
Update statement
End Try
Begin Catch
Set @ErrorMessage = 'Update failed'
raiserror 99998 @ErrorMessage
Return 1
End Catch
Test Scripts
In order to execute a stored procedure and capture all information that may be passed back, a script should be created. The script should initialize variables to capture the return value from the Return statement and any Output Parameters that are used. The variables can then be used within the calling program or displayed with a Select statement.
Here is a test script that calls a procedure that has a Return value, two Output Parameters and also a Result Set:
-- Test Script for spRegionalYearlyProductSales
-- Variable declaration
-- Variable to capture the value from the Return command
Declare @ReturnValue As int
Declare @TotalQuantity As int
Declare @TotalSales As money
-- Execute the procedure.
Exec @ReturnValue = spRegionalYearlyProductSales
@RegionName = 'North Central',
@ProductName = 'BPWrite',
@Year = '2000',
@TotalQuantity = @TotalQuantity output,
@TotalSales = @TotalSales output
-- Display the total values and return value.
Select @TotalQuantity As 'Total Quantity Sold'
Select @TotalSales As 'Total Dollar Value'
Select @ReturnValue As 'Return Value'
GO
Putting It All Together
Here is a sample procedure (for the above test script) that contains comments, code blocks, input and output parameters, parameter validation, error handling, print commands, return values and a returned result set:
CREATE PROCEDURE spRegionalYearlyProductSales
/*
This procedure calculates total sales and quantity sold
given three input parameters region, product and year.
The total sales and quantity sold are returned in two output
parameters.
Also a customer list containing customer name, quantity
purchased and dollar value of purchases is returned.
Input Parameters:
RegionName
ProductName
Year
Output Parameters:
TotalSales
TotalQuantity
Developer: Gary Mitchell
Date Created: 5/3/2009
Date Last Updated: 5/6/2009
*/
-- Input Parameters
-- The RegionName parameter is used in the where clause of the queries to limit records.
@RegionName varchar(35) = Null,
-- The ProductName parameter is used in the where clause of the queries to limit records.
@ProductName varchar(35) = Null,
-- The Year parameter is used in the where clause of the queries to limit records.
@Year char(4) = Null,
-- Output Parameters
-- The TotalQuantity parameter is used to pass back the total
–- number of units sold.
@TotalQuantity integer output,
-- The TotalSales parameter is used to pass back the total sales
-- in dollars of the units sold.
@TotalSales money output
As
Begin
-- Variable declaration
-- The BeginDate is created using the year parameter and is
-- used with the EndDate in the queries to gather data
-- for the year.
Declare @BeginDate smalldatetime
-- The EndDate is created using the year parameter.
Declare @EndDate smalldatetime
-- The ErrorMessage is used to pass information back to the user.
Declare @ErrorMessage varchar(100)
-- Rows is used to determine if any customer list data was found.
Declare @Rows tinyint
-- Parameter Validation
-- Check if year was supplied.
If @Year Is Null
Begin
raiserror 99999 'You must supply a year'
-- Display a hint on how to run the procedure.
Print 'Usage: Exec spRegionalYearlyProductSales region name,
product name, year, @TotalSales output,
@TotalQuantity output'
Return 1
End
-- Use implicit conversion to date to check if valid year was entered.
Begin Try
Set @BeginDate = @Year + '-01-01'
Set @EndDate = @Year + '-12-31'
End Try
Begin Catch
Set @ErrorMessage = 'Year ' + @Year + ' is not valid'
raiserror 99998 @ErrorMessage
Print 'Usage: Exec spRegionalYearlyProductSales region name,
product name, year, @TotalSales output,
@TotalQuantity output'
Return 1
End Catch
-- Check if a Region Name was entered
If @RegionName Is Null
Begin
raiserror 99997 'You must supply a region name'
Print 'Usage: Exec spRegionalYearlyProductSales region name,
product name, year, @TotalSales output,
@TotalQuantity output'
Return 1
End
-- Check to see if Region Name is valid.
If (Select COUNT(*)
From Regions
Where RegionName = @RegionName) = 0
Begin
Set @ErrorMessage = 'Region ' + @RegionName + ' is not valid'
raiserror 99996 @ErrorMessage
Print 'Usage: Exec spRegionalYearlyProductSales region name,
product name, year, @TotalSales output,
@TotalQuantity output'
Return 1
End
-- Check if a Product Name was entered.
If @ProductName Is Null
Begin
raiserror 99995 'You must supply a product name'
Print 'Usage: Exec spRegionalYearlyProductSales region name, product name, year, @TotalSales output, @TotalQuantity output'
Return 1
End
-- Check if Product Name is valid.
If (Select COUNT(*)
From Products
Where ProductName = @ProductName) = 0
Begin
Set @ErrorMessage = 'Product ' + @ProductName + ' is not valid'
raiserror 99994 @ErrorMessage
Print 'Usage: Exec spRegionalYearlyProductSales region name, product name, year, @TotalSales output, @TotalQuantity output'
Return 1
End
-- Query to populate the output parameters - TotalQuantity and TotalSales.
Select @TotalQuantity = SUM(OI.Qty),
@TotalSales = SUM(OI.Price * OI.Qty)
From Regions R
Inner Join RegionStates RS On RS.RegionID = R.RegionID
Inner Join Customers C On C.State = RS.State
Inner Join Orders O On O.CustomerID = C.CustomerID
Inner Join OrderItems OI On OI.InvoiceID = O.InvoiceID
Inner Join Products P On P.ProductID = OI.ProductID
Where R.RegionName = @RegionName
And P.ProductName = @ProductName
And O.OrderDate Between @BeginDate And @EndDate
-- Query for the list of customers who made purchases matching
-- the input parameters.
-- The customer list includes Customer Name, Quantity Purchased
-- and Total Dollar Value Purchased.
Select C.CustomerName As "Customer Name",
SUM(OI.Qty) As "Quantity Purchased",
SUM(OI.Price * OI.Qty) As "Total Dollar Value"
From Regions R
Inner Join RegionStates RS On RS.RegionID = R.RegionID
Inner Join Customers C On C.State = RS.State
Inner Join Orders O On O.CustomerID = C.CustomerID
Inner Join OrderItems OI On OI.InvoiceID = O.InvoiceID
Inner Join Products P On P.ProductID = OI.ProductID
Where R.RegionName = @RegionName
And P.ProductName = @ProductName
And O.OrderDate Between @BeginDate And @EndDate
Group By C.CustomerName
-- Check the number of rows returned by the above query to
-- and disply a not found error if no rows were returned.
Set @Rows = @@ROWCOUNT
If @Rows <=0
Begin
Set @ErrorMessage = 'The combination of Region '+ @RegionName
+ ', Product ' + @ProductName
+ ', and Year ' + @Year + ' could not be found.'
raiserror (@ErrorMessage, 16, 1)
Return 1
End
End
GO
Add Article Text...