HOUR 12. Using T-SQL to Design SQL Server Stored Procedures

Stored procedures are at the heart of any client/server application. Using stored procedures, you can guarantee that processing is completed on the server. Stored procedures have many other benefits as well, including the following:

• Stored procedures help you to separate the client application from the database’s structure.

• Stored procedures help you to simplify client coding.

• Stored procedures process at the server (reduces required bandwidth).

• Stored procedures enable you to create reusable code.

• Stored procedures enable you to perform error-handling at the server.

• Stored procedures facilitate the security of data.

• Because stored procedures are pre-compiled, they execute more quickly.

• Stored procedures improve the application’s stability.

• Stored procedures reduce network locking.

• When you build a stored procedure, a query plan is created. This query plan contains the most efficient method of executing the stored procedure given available indexes and so on.

---

In this hour you’ll learn:

• The basics of working with stored procedures

• How to declare and work with variables

• How to control the flow of the stored procedures that you write

The Basics of Working with Stored Procedures

Creating stored procedures in SQL Server 2005 Express is easy. You can create a stored procedure with the Query Editor or with T-SQL.

Designing a Stored Procedure in the Query Editor

Although you may not be able to design an entire stored procedure in the Query Editor, you will probably find it easiest to use the Query Editor to design the T-SQL statement that you include in your stored procedure. Take the steps that follow to build the SELECT statement that you want to include in your stored procedure:

1. Expand the Programmability node of the database that you are working with so that you can see the Stored Procedures node underneath it (see Figure 12.1).

Figure 12.1. The Stored Procedures node appears under the Programmability node.

2. Right-click the Stored Procedure node and select New Stored Procedure. A template for a stored procedure appears in the Query Editor (see Figure 12.2).

Figure 12.2. When you create a stored procedure, Management Studio provides you with a template for that stored procedure.

3. Click and drag over the SELECT statement included in the template and press the Delete key to delete it.

4. Right-click in that same location and select Design Query in Editor. The Add Table Dialog appears (see Figure 12.3).

Figure 12.3. You replace the template SQL statement with the SQL statement generated by the Query Editor.

5. Add the desired tables to the query by selecting each table and clicking Add.

6. Click Close to complete the process. The Query Designer should look similar to Figure 12.4.

Figure 12.4. The Query Designer appears with the selected tables.

7. Click to add the desired fields to the SELECT statement. Click OK when finished. The stored procedure should appear as in Figure 12.5.

Figure 12.5. The generated SQL statement appears within the stored procedure template.

8. Modify the stored procedure, adding all the functionality you want it to have.

9. After completing the stored procedure, click Execute to execute the CREATE PROCEDURE statement.

10. You need to save only if you want to store the T-SQL behind the stored procedure in an external file.

---

By the Way

To make the generated stored procedure execute, you must provide the stored procedure with a name, and delete all the superfluous template code, such as the declaration of parameters.

---

Using T-SQL to Create a Stored Procedure

Sometimes you may not find it useful to use the Query Editor to get started in creating a stored procedure. In that case you will simply create the stored procedure and begin modifying the template as desired. Here are the steps involved:

1. Expand the Programmability node of the database that you are working with so that you can see the Stored Procedures node underneath it.

2. Right-click the Stored Procedure node and select New Stored Procedure. A template for a stored procedure appears in the Query Editor.

3. Type the body of the stored procedure.

4. After completing the stored procedure, click Execute to execute the CREATE PROCEDURE statement.

5. You need to save only if you want to store the T-SQL behind the stored procedure in an external file.

Declaring and Working with Variables

Just as you can create variables within the subroutines and functions that you build, you can also declare variables in your stored procedures. You use the keyword DECLARE to create a variable. The syntax looks like this:

DECLARE @VariableName DataType [(length)], @VariableName DataType [(length)]

Here’s an example:

DECLARE @FirstName VarChar(35)

Unintialized variables are assigned the value Null. You use a SELECT statement to assign a value to a variable. It looks like this:

SELECT @FirstName = 'Alexis'

The following is a stored procedure that illustrates the use of a variable:

DECLARE @strCompany varchar (50)
SELECT @strCompany = Upper(CompanyName)
FROM Customers
WHERE CustomerID = 'ALFKI'
SELECT @strCompany

The example declares a variable called @strCompany. The code stores the uppercase version of the CompanyName associated with the customer ALFKI into the variable. The procedure returns the variable in a SELECT. Figure 12.6 illustrates the result of executing the sample stored procedure.

Figure 12.6. An example of a variable used in a stored procedure.

Controlling the Flow

Often you want specified statements in your stored procedure to execute only if certain conditions are true. T-SQL contains several constructs that enable you to control the flow of your stored procedures. These include BEGIN...END, IF...ELSE, GOTO, RETURN, CASE, and WHILE. The sections that follow cover each of these constructs.

Using IF...ELSE

You use the IF...ELSE construct to make a decision within the stored procedure. This decision is generally based on parameters supplied to the stored procedure. The IF...ELSE construct works like this:

IF (SomeCondition)
    BEGIN
        --Execute multiple statements
    END

With this version of the IF...ELSE construct, certain statements execute only if the condition is true. No special statements execute if the condition is false. The construct that follows accommodates the scenario when the condition is false:

IF (SomeCondition)
    BEGIN
        --Execute multiple statements
    END
ELSE
    BEGIN
        --Execute multiple statement
    END

Here’s an example of an IF...THEN construct in action:

DECLARE @Locale VarChar(20), @Country VarChar(30)

SELECT @Country = Country
    FROM Customers
    WHERE CustomerID = 'ALFKI'

IF @Country   = 'USA'
    BEGIN
        SELECT @Locale = 'Domestic'
    END
ELSE
    BEGIN
       SELECT @Locale= 'Foreign'
    END

SELECT CustomerID, @Locale
    FROM Customers
    WHERE CustomerID = 'ALFKI'

The code begins by declaring two variables: @Locale and @Country. It stores the contents of the Country field for the customer ALFKI into the @Country variable. It then evaluates the contents of the @Country variable, storing the appropriate locale into the @Locale variable. Finally, it returns the CustomerID and the contents of the @Locale variable for the customer with the CustomerID ALFKI. Figure 12.7 provides an example of executing the stored procedure.

Figure 12.7. An example of executing a stored procedure that includes an IF...ELSE construct.

---

By the Way

It is important to note that if you want to execute more than one statement under a specific condition, you must enclose the statements within the BEGIN...END construct (see the following section).

---

Working with BEGIN...END

The BEGIN...END construct enables you to group a series of statements together. Without the BEGIN...END construct, only the first statement after the IF or the ELSE executes. Consider the following example:

DECLARE @Locale VarChar(20), @Country VarChar(30)

SELECT @Country = Country
    FROM Customers
    WHERE CustomerID = 'ALFKI'

IF @Country   = 'USA'
    BEGIN
        SELECT @Locale = 'Domestic'
        PRINT 'This is domestic'
        PRINT ' '
        PRINT 'Hello there'
    END
ELSE
    BEGIN
        SELECT @Locale= 'Foreign'
        PRINT 'This is foreign'

        PRINT ' '
        PRINT 'Hello there'
    END

SELECT CustomerID, @Locale
    FROM Customers
    WHERE CustomerID = 'ALFKI'

In this example, multiple statements execute if the condition is true, and multiple statements execute if the condition is false. Without the BEGIN...END construct after the IF, the code renders an error. Figure 12.8 shows this stored procedure in action.

Figure 12.8. An example of executing a stored procedure that includes a BEGIN...END construct.

Exploring GOTO, RETURN, and Labels

You use the GOTO statement to jump to a label in your stored procedure. Programmers seem to use this statement most commonly in error handling. The RETURN statement unconditionally exits the stored procedure without executing any other statements. These three keywords are covered together because they generally work as a group. Consider the following examples:

IF Month(GetDate()) > 6
    BEGIN
        PRINT 'In IF Statement'
        GOTO MyLabel
    END

SELECT CustomerID, CompanyName FROM Customers

MyLabel:
    SELECT  OrderID, OrderDate FROM Orders

This example evaluates to see whether the month associated with the current date is greater than the value six. If it is, the statement In IF Statement appears and code execution jumps to the label MyLabel. The procedure then selects data from the Orders table. If the month associated with the current date is less than or equal to the value six, the procedure first selects data from the Customers table. Code execution then continues at the label, where the procedure selects data from the orders table. Figure 12.9 shows an example of executing the code when the month associated with the current date is greater than six. Figure 12.10 shows the sample procedure where the month associated with the current date is less than or equal to six.

Figure 12.9. An example of executing a stored procedure that includes GOTO and a label.

Figure 12.10. An example of executing the same stored procedure with a different value for the month of the current date.

As mentioned, a RETURN statement unequivocally exits from the procedure. Take a look at the procedure that follows:

IF Month(GetDate()) > 6
    BEGIN
        PRINT 'In IF Statement'
        GOTO MyLabel
    END
SELECT CustomerID, CompanyName FROM Customers
RETURN

MyLabel:
    SELECT  OrderID, OrderDate FROM Orders

In this example, if the month associated with the current date is greater than the value six, the In IF Statement message appears and then the procedure returns data from the Orders table. If the month associated with the current date is less than or equal to the value six, the code selects data from the Customers table and then exits the procedure. Because of the RETURN statement, the procedure does not select data from the Orders table. Figure 12.11 shows an example of executing the code when the month associated with the current date is less than or equal to six. Figure 12.12 shows the sample procedure where the month associated with the current date is greater than six.

Figure 12.11. An example of executing a stored procedure that includes the RETURN statement.

Figure 12.12. An example of executing the same stored procedure with a different value for the month of the current date.

Working with the CASE Statement

Most developers use the CASE statement to compare a result from a SQL statement against a set of simple responses. The CASE statement replaces a table value with an alternate value. The CASE statement looks like this:

CASE InputExpression
    WHEN WhenExpression THEN ResultExpression
        [...n]
    [ELSE ElseResultExpression]
END

Here’s an example of this use of a case statement:

SELECT OrderID, OrderDate,
    CASE ShipVIA
        WHEN 1 THEN 'UPS'

        WHEN 2 THEN 'FedEx'
        WHEN 3 THEN 'U.S. Mail'
    END AS Shipper,
    Freight FROM Orders

The expression selects the OrderID, OrderDate, and Freight fields from the Orders table. The CASE statement evaluates the contents of the ShipVia field. It returns an appropriate string depending on the value in the ShipVia field. Figure 12.13 illustrates this example.

Figure 12.13. An example of executing a CASE statement replaces a table value with an alternate value.

The second use of the CASE construct looks like this:

CASE
    WHEN Expression THEN TruePart
    ELSE FalsePart
END

Here’s an example:

DECLARE @AverageFreight Money
SELECT @AverageFreight = AVG(Freight) FROM Orders

SELECT OrderID, OrderDate, Freight,
    CASE
        WHEN FREIGHT <= @AverageFreight
        THEN 'Low Freight'
        ELSE 'High Freight'
    END AS Shipper,
    Freight FROM Orders

The example first declares the @AverageFreight variable. It sets the variable equal to the average freight amount from the Orders table. The CASE statement evaluates whether the freight of the current row is less or equal to the average freight amount. If so, the statement returns Low Freight. Otherwise, it returns High Freight. This value is combined with the OrderID, OrderDate, and Freight amounts which are also selected from the table. Figure 12.14 shows this use of the CASE statement in action.

Figure 12.14. An alternative use of the CASE statement.

Exploring the WHILE Statement

You use the WHILE statement when you want to set up a loop. The loop continues to execute until the specified condition is met. The WHILE construct looks like this:

WHILE BooleanExpression
    (SQLStatement | SQLBlock)

Here’s an example:

CREATE TABLE MyTable
(
LoopID INT,
LoopText VarChar(25)
)

DECLARE @LoopValue INT
DECLARE @LoopText CHAR(25)

SELECT @LoopValue = 1

WHILE (@LoopValue < 100)
BEGIN
    SELECT @LoopText = 'Iteration #' + Convert(VarChar(25), @LoopValue)
    INSERT INTO MyTable(LoopID, LoopText)
        VALUES (@LoopValue, @LoopText)
    SELECT @LoopValue = @LoopValue + 1
END

SELECT * FROM MyTable

The routine first creates a table called MyTable. The table contains two fields: an INT field and a CHAR field. The routine then declares two variables, an INT variable and a CHAR variable. It sets the value of the @LoopValue variable to one. The code then loops from one to one hundred. As it loops, it sets the CHAR variable equal to the text Iteration # combined with the contents of the @LoopValue variable converted to a VarChar. Next it inserts the contents of the @LoopValue and @LoopText variables into the table. Finally it increments the value of the @LoopValue variable. Figure 12.15 shows the results of executing the WHILE statement.

Figure 12.15. With a WHILE statement the loop continues to execute until the specified condition is met.

Summary

Stored procedures have many benefits. In this hour you learned the ins and outs of working with stored procedures. You learned the basics of stored procedures, and how to declare and work with variables. Finally, you learned numerous ways that you can control the flow of the stored procedures that you write.

Q&A

Workshop

Quiz

Quiz Answers

Activities

Use the SQL Express Manager to practice creating a stored procedure. Build a procedure that determines whether the ContactTitle in the Customer table in the Northwind database is Owner. If the ContactTitle is Owner, store Head Honcho into a variable called @TitleName. If the ContactTitle is not Owner, store Peon into the @TitleName variable. Finally, return the contents of the CustomerID field and the @TitleName variable for the customer with the CustomerID of ALFKI.