Lesson 16. Stored Procedure Special Topics
You should be aware of some special topics when writing your stored procedures. In some cases, understanding these topics can help you provide a more efficient solution to a problem. In other cases, these topics address functionality available only with the techniques covered during this lesson. In this lesson, you find out:
How to work with stored procedures and temporary tables
How to utilize cursors in the stored procedures you build
How stored procedures interact with the security of your database
Stored Procedures and Temporary Tables
SQL Server creates temporary tables in a special system database called TempDB. SQL Server creates TempDB each time it starts and destroys it each time it shuts down. SQL Server uses TempDB to house many temporary objects that it needs to run. You can use TempDB to share data between procedures or to help you to accomplish complex tasks. You often will need to incorporate temporary tables into the stored procedures that you write. Here’s an example of how you create and use a temporary table:
CREATE PROCEDURE procEmployeesGetTemp AS
BEGIN
CREATE TABLE #TempEmployees
(BusinessEntityID int NOT NULL PRIMARY KEY,
JobTitle varchar(50),
HireDate datetime,
VacationHours int)
INSERT INTO #TempEmployees
(BusinessEntityID, JobTitle, HireDate, VacationHours)
EXEC procEmployeesGetByJobTitleAndHireDate '1/1/2009', 'Production Technician – WC20'
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours
FROM #TempEmployees
ORDER BY HireDate DESC
END
This procedure uses a second procedure called procEmployeesGetByHireDateAndJobTitleAndHireDate. It looks like this:
CREATE PROCEDURE procEmployeesGetByJobTitleAndHireDate
@HireDate DateTime,
@JobTitle varchar(50)
AS
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours
FROM HumanResources.Employee
WHERE HireDate >= @HireDate AND
JobTitle = @JobTitle
ORDER BY HireDate DESC
The procEmployeesGetByHireDateAndJobTitleAndHireDate procedure receives two parameters. It uses those parameters for the WHERE clause for HireDate and JobTitle. The procEmployeesGetTemp procedure creates a temporary table that holds customer information. It inserts into the temporary table the results of executing the procEmployeesGetByHireDateAndJobTitle procedure, passing it 1/1/2009 and Design Engineer as the hire date and job title values. Finally, the procedure selects data from the temporary table, ordering the result by HireDate in descending order. The results appear in Figure 16.1. Notice that only employees with the title Production Technician – WC20 and a hire date on or after 1/1/2009 appear.
FIGURE 16.1 The stored procedure outputs data contained in a temporary table that is populated by the results of executing a second stored procedure.
Stored Procedures and Cursors
A SQL Server SELECT statement outputs a complete result set. There are times when you will want to process one row at a time, performing some operation on each row. Opening a cursor on a result set allows you to process each row in the result set independently.
You use the DECLARE CURSOR statement to define a SQL Server cursor. The parameters to the DECLARE CURSOR statement allow you to determine the behavior of the cursor. For example, you can designate a cursor as read-only or as forward-only.
The OPEN statement populates the cursor with the data in the result set. FETCH returns a single row from the result set. You use CLOSE to release the result set associated with the cursor and DEALLOCATE to release all resources associated with the cursor. The following example illustrates the process of working with a cursor:
CREATE PROCEDURE procEmployeesGetCursor
AS
BEGIN
DECLARE @BusinessEntityID int,
@HireDate datetime,
@JobTitle varchar(5)
DECLARE EmployeeCursor CURSOR FOR
SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
WHERE HireDate >= '1/1/2009'
ORDER BY HireDate DESC
OPEN EmployeeCursor
FETCH NEXT FROM EmployeeCursor INTO
@BusinessEntityID, @JobTitle, @HireDate
--As long as there are more rows to fetch
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'BusinessEntityID: ' +
CONVERT(varchar(10), @BusinessEntityID) +
' – ' + UPPER(@JobTitle) +
' Hire Date = ' + CONVERT(varchar(10), @HireDate)
FETCH NEXT FROM EmployeeCursor
END
CLOSE EmployeeCursor
DEALLOCATE EmployeeCursor
END
The procedure procEmployeesGetCursor first declares variables used by the cursor. It then retrieves data from the Employee table. After opening the EmployeeCursor cursor, it uses the FETCH NEXT statement to retrieve the first row from the result set, placing its data into the three variables. The @@FETCH_STATUS determines if there are more rows to fetch from the cursor. If there are, the code prints data from the cursor. It then fetches the next row from the cursor. Finally, the code closes and deallocates the cursor, freeing the resources associated with it. Figure 16.2 provides an example of executing the stored procedure.
FIGURE 16.2 The stored procedure utilizes a cursor to loop through the Employee table, outputting data from each row in the table.
The stored procedure used in the previous example provided a simple illustration of how you can use a cursor to loop through records in a result set. The stored procedure that follows shows how you can use a cursor to solve a real-life data problem. Specifically, it allows you to search for a specific string with any text-type columns in a table. It looks like this:
CREATE PROCEDURE procGetString
@FindString varchar(8000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQLCommand varchar(8000)
DECLARE @Where varchar(8000)
DECLARE @ColumnName sysname
DECLARE @Cursor varchar(8000)
SET @SQLCommand = 'SELECT * FROM Person.Address'
SET @Where = ''
SET @Cursor = 'DECLARE FindCursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ' + '"Person"' + ' AND
TABLE_NAME = ' + '"Address"' + ' AND
DATA_TYPE IN ("char", "nchar", "ntext",
"nvarchar", "text", "varchar")'
EXEC (@Cursor)
OPEN FindCursor
FETCH NEXT FROM FindCursor INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Where <> ''
BEGIN
SET @Where = @Where + ' OR '
END
SET @Where = @Where + ' [' + @ColumnName +
'] LIKE "'+ @FindString + '"
FETCH NEXT FROM FindCursor INTO @ColumnName
END
SET @SQLCommand = @SQLCommand + ' WHERE ' + @Where
EXEC (@SQLCommand)
CLOSE FindCursor
DEALLOCATE FindCursor
END
This procedure is designed to accept a string as a parameter. It then searches for that string in all text-type columns within the Person.Address table. After declaring four variables, the procedure uses a SET statement to assign the beginning of a SELECT statement to the @SQLCommand variable. The SET statement is an alternative way (the other being SELECT) to assign a value to a variable.
Probably the most complex statement in the procedure is the SET statement that creates the DECLARE CURSOR statement and assigns it to the @Cursor variable. This statement sets the @Cursor variable equal to a string that declares a cursor that is responsible for looping through all the text-type columns (char, nchar, ntext, nvarchar, text, and varchar) in the Person.Address table. The EXEC (@Cursor) statement executes the contents of the @Cursor variable, thereby creating the FindCursor cursor.
As the code loops through the result set, it builds a WHERE clause containing all of the text-type fields and the contents of the @FindString variable. Finally, it sets the @SQLCommand variable equal to the SELECT clause concatenated with the WHERE clause. Figure 16.3 provides an example of the results of executing the stored procedure passing the parameter %Wood%. Notice in the results that rows that contain Wood in either the AddressLine1 field or the City field appear in the output.
FIGURE 16.3 The stored procedure utilizes a cursor to search for a string within any text-type field in the Address table.
Stored Procedures and Security
Like views, stored procedures provide an excellent tool for securing your application’s data. You can grant rights to a stored procedure without providing any rights to the underlying table(s). Consider the following scenario. Imagine you create an unbound Access form where users can select various customers. You then execute stored procedures to insert, update, and delete data. In this scenario, you can grant users view rights to the Customers table. You do not need to grant them insert, update, or delete rights to the table. Instead you grant them execute rights to the appropriate stored procedures. In this way, you can allow your users to easily create queries and reports that use the Customer data (you granted them view rights to the table). They can only insert, update, and delete data via the stored procedures that you call from your application code.
Summary
There are some special topics you should be aware of when working with stored procedures. This lesson began by talking about how you can use temporary tables to enhance the stored procedures that you build. It then continued with a discussion of cursors where you saw two solid examples of how you can use cursors to loop through and perform some operation on the result set of a SELECT statement. Finally, the lesson discussed the implications of stored procedures on the security of your database and the data contained within it.
Q&A
Q. Why would you create temporary tables?
A. You create temporary tables to share data between procedures or to help you to accomplish complex tasks.
Q. What does a cursor do?
A. A cursor allows you to loop through a result set, performing some operation on each record individually.
Q. What role do stored procedures take in allowing you to secure your table data?
A. Using stored procedures, you can grant rights to the stored procedure without granting any rights to the underlying tables.
Workshop
Quiz
1. What database does SQL Server use for temporary tables?
2. With stored procedures, the user must have rights to the tables that are affected by the stored procedure (true/false).
3. What keywords do you use to create a cursor?
Quiz Answers
1. TempDB.
2. False. They need rights to only the stored procedure.
3. DECLARE mycursor CURSOR FOR.
Activities
Build a stored procedure that creates a temporary table containing PurchaseOrderID, EmployeeID, OrderDate, ShipDate, and Freight. Execute a second stored procedure that selects those fields from the Purchasing.PurchaseOrderHeader table, ordering by EmployeeID and OrderDate. The first stored procedure should pass the second stored procedure a parameter for the freight amount. The second stored procedure receives the parameter for the freight amount. It selects all fields from the Orders table where Freight is greater than the parameter value, ordering by EmployeeID and Freight. It returns the result into the temporary table in the first stored procedure. Finally, have the first stored procedure display the contents of the temporary table. For help, consult the example in the “Stored Procedures and Temporary Tables” section of this lesson.