HOUR 14. 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 to provide a more efficient solution to a problem. In other cases, these topics address functionality available only with the techniques covered during this hour. In this hour you’ll learn about:

• How to work with stored procedures and temporary tables

• How stored procedures interact with the security of your database

• How to build and work with user-defined functions

• How to create and work with triggers

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 procCustomersGetTemp AS
BEGIN
CREATE TABLE #TempEmployees
(EmployeeID int NOT NULL PRIMARY KEY,
Title varchar(50),
HireDate varchar (50),
Rate varchar (50))
INSERT INTO #TempCustomers
(EmployeeID, Title, HireDate, Rate)
EXEC procEmployeesGetByHireDateAndRate '>#', ''

SELECT Employ, ContactName, ContactTitle, City
FROM #TempCustomers
ORDER BY City, CompanyName
END

This procedure uses a second procedure called procCustomersGetByCountryAndTitle. It looks like this:

CREATE PROCEDURE procCustomersGetByCountryAndTitle
    @CountryName VarChar(50),
    @ContactTitle VarChar(50)
AS
SELECT CompanyName, ContactName, ContactTitle, City
FROM Customers
WHERE Country = @CountryName AND
ContactTitle = @ContactTitle
ORDER BY City, CompanyName

The procCustomersGetByCountryAndTitle procedure receives two parameters. It uses those parameters for the WHERE clause for Country and ContactTitle. The procCustomersGetTemp procedure creates a temporary table that holds customer information. It inserts into the temporary table the results of executing the procCustomersGetByCountryAndTitle procedure, passing it USA and Owner as the country and title values. Finally, the procedures select data from the temporary table, ordering the result by City and CompanyName.

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 that 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.

Building and Working with User-Defined Functions

SQL 2000 introduced user-defined functions. User-defined functions add power and flexibility previously unavailable with views and stored procedures. Three types of user-defined functions exist: scalar, inline table-valued, and multi-statement table-valued. The sections that follow cover each of these types of user-defined functions in detail.

Scalar Functions

Scalar functions return a single value of the type defined in the RETURNS clause. The body of the function is between a BEGIN and END block. Scalar functions can return any data type except text, ntext, image, cursor, or timestamp. Here’s an example of a scalar function:

CREATE FUNCTION dbo.FullName
    (@FirstName nVarChar(10),
    @LastName nVarChar(20))
RETURNS nVarChar(35)
BEGIN
    RETURN (@LastName + ', ' + @FirstName)
END

This function receives two parameters: @FirstName and @LastName. It returns an nVarChar(35) value. The return value is the combination of the @LastName and @FirstName input parameters combined with a comma and a space. You could call the function like this:

SELECT FirstName, LastName, dbo.FullName(FirstName, LastName) FROM Employees

The example displays the FirstName, LastName and result of the FullName function (see Figure 14.1).

Figure 14.1. Scalar functions return a single value of the type defined in the RETURNS clause.

Inline Table-Valued Functions

As their name implies, inline table-valued functions return a table. Inline table-valued functions have no body. They simply return the result of a simple SELECT statement. Here’s an example:

CREATE FUNCTION dbo.CustGetByTitle
    (@Title nVarChar(30))
RETURNS Table
AS
RETURN SELECT CustomerID, CompanyName, ContactName, City, Region
FROM Customers WHERE ContactTitle = @Title

This example receives a parameter called @Title. It returns a table containing selected fields from the Customers table where the ContactTitle equals the @Title parameter value. You would call the function like this:

SELECT * FROM dbo.CustGetByTitle('Owner')

The example selects all fields from the table returned from the CustGetByTitle function. Because the example passes Owner as a parameter, only the owners are included in the result set (see Figure 14.2).

Figure 14.2. Inline table-valued functions return a table.

Multi-Statement Table-Valued Functions

Multi-statement table-valued functions are similar to inline table-valued functions. The main difference is that like scalar functions, they have a body defined by a BEGIN...END block. Like inline table-valued functions, they return a table.

Creating and Working with Triggers

A trigger is like an event procedure that runs when data changes. You can create triggers that execute in response to inserts, updates, and deletes. Developers use triggers to enforce business rules and even to perform tasks such as inserting data into an audit log.

Creating Triggers

To create or modify a trigger:

1. Click to expand the node for the table to which you want to add the trigger. The Triggers node appears (see Figure 14.3).

Figure 14.3. The Triggers node shows you the existing triggers associated with a table.

2. Right-click the Triggers node and select New Trigger. A new query window appears, enabling you to type the text for the trigger (see Figure 14.4).

Figure 14.4. The new query window enables you to type the T-SQL that comprises the trigger.

3. Type the T-SQL that comprises the Trigger.

4. Execute the CREATE TRIGGER statement (see Figure 14.5). After refreshing the list of triggers, it appears under the list of triggers associated with that table (see Figure 14.6).

Figure 14.5. You must execute the T-SQL to create the trigger.

Figure 14.6. After it is created, a trigger appears under the list of triggers available for that table.

The syntax for a trigger is

CREATE TRIGGER TriggerName
    On TableName
    FOR [INSERT], [UPDATE], [DELETE]
    AS
    --Trigger Code

Here’s an example:

CREATE TRIGGER NoDeleteActive ON [dbo].[Products]
FOR DELETE
AS

DECLARE @Discontinued Bit
SELECT @Discontinued = Discontinued FROM deleted

IF @Discontinued = 0
    BEGIN
        ROLLBACK TRAN
        RAISERROR('Active Product Cannot Be Deleted',16,1)
    END

This trigger evaluates to see whether the product the user is attempting to delete is discontinued. If it is, SQL Server aborts the delete process and displays an error message.

The following is an example of a trigger that inserts data into an audit log whenever the user updates a row:

CREATE TRIGGER InsertProductAudit ON dbo.Products
FOR UPDATE
AS
DECLARE  @ProductID int, @ProductName NVarChar(40),
        @UnitPrice Money, @UnitsInStock SmallInt
SELECT @ProductID = ProductID, @ProductName = ProductName,
        @UnitPrice = UnitPrice, @UnitsInStock = UnitsInStock FROM inserted
INSERT ProductsAudit VALUES (@ProductID, @ProductName, @UnitPrice,@UnitsInStock)

This example inserts data into an audit log whenever the user modifies data in the Products table.

INSTEAD OF Triggers

An INSTEAD OF trigger fires in place of the triggering action. It executes after SQL Server creates the inserted and deleted tables, but before SQL Server takes any other actions. SQL Server executes INSTEAD OF triggers before constraints are applied. This enables you to perform pre-processing that supplements existing constraints.

Downsides of Triggers

Many developers avoid triggers entirely. Probably the biggest disadvantage of triggers is that they get buried in your database and are difficult to debug and troubleshoot. This has been ameliorated slightly in SQL Server 2005 Express because triggers appear as a node under the table that they are associated with. Triggers also slow down database operations. Furthermore, they often lock data for relatively long periods of time, increasing the chance of concurrency problems. For these reasons, most developers opt to utilize stored procedures functions, or even middle-tier components to replace the role of triggers in the applications that they build.

Summary

There are some special topics that you should be aware of when working with stored procedures. These topics not only include options available when you work with stored procedures, but they also provide you with some alternatives to stored procedures. In this hour we began by talking about how you can use temporary tables to enhance the stored procedures that you build. We then discussed the implications of stored procedures on the security of your database and the data contained within it. You learned about an alternative to stored procedures called user-defined functions. User-defined functions are a close relative to stored procedures. Finally, you learned about another cousin of stored procedures called triggers. Armed with this arsenal of techniques, you are ready to create any database application.

Q&A

Workshop

Quiz

Quiz Answers

Activities

Build a stored procedure that creates a temporary table containing OrderID, CustomerID, OrderDate, and Freight. Execute a second stored procedure that selects those fields from the Orders table, ordering by CustomerID 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 CustomerID 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 chapter.