Lesson 2:Creating, Executing, Modifying, and Deleting Stored Procedures | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

In this lesson, you will learn a number of methods for managing stored procedures. Stored procedures are commonly created, executed, modified, or deleted using Query Analyzer or Enterprise Manager.

Stored procedures can be created before the objects that they reference are created. This feature is called deferred name resolution. The keywords used to create a procedure are CREATE PROCEDURE.

Before running a stored procedure, you must provide any required parameter values. Stored procedures can be executed manually or automatically when SQL Server starts. The keyword used to run a procedure is EXECUTE. This keyword is optional if the procedure to run is a single line of code or if the procedure name is the first word in a batch.

Modifying a stored procedure after it is created is common practice; perhaps you need to add a parameter or change some other part of the code in the procedure. Modifying a procedure, rather than deleting it and recreating it, is a time-saver, because many of the stored procedure properties (such as permissions) are retained when a procedure is modified. The keywords used to modify a procedure are ALTER PROCEDURE.

Procedures are dropped from a database with the DROP keyword. You can drop a stored procedure from Enterprise Manager or Query Analyzer by selecting the stored procedure and pressing the DELETE key. A procedure shouldn't be deleted until other objects that depend on the procedure are removed or modified (to break their dependency).

After this lesson, you will be able to:

Create and modify a stored procedure in SQL Server.
Execute a stored procedure.
Delete a stored procedure from SQL Server.

Estimated lesson time: 45 minutes

How a Procedure Is Stored

When a procedure is created, SQL Server checks the syntax of the Transact-SQL statements within it. If the syntax is incorrect, SQL Server will generate a "syntax incorrect" error message, and the procedure will not be created. If the procedure's text passes syntax checking, the procedure is stored by writing its name and other information (such as an auto-generated identification number) to the SysObjects table. The text used to create the procedure is written to the SysComments table of the current database.

The following SELECT statement queries the SysObjects table in the Pubs database to show the identification number of the ByRoyalty stored procedure:

 SELECT [name], [id] FROM [pubs].[dbo].[SysObjects] WHERE [name] = 'byroyalty'

This query returns the following:


name	id
byroyalty	581577110

Using the information returned from the SysObjects table, the next SELECT statement queries the SysComments table by using the identification number of the ByRoyalty stored procedure:

 SELECT [text] FROM [pubs].[dbo].[SysComments] WHERE [id] = 581577110

This query returns the create text of the ByRoyalty stored procedure, whose identification number is 581577110.

NOTE

The two previous SELECT statements could have been consolidated into a single SELECT statement by using a JOIN. They are separated here for simplicity and clarity.

Using the sp_helptext system stored procedure is a better option for displaying the text used to create an object (such as an unencrypted stored procedure), because the text is returned in multiple rows. You used the sp_helptext stored procedure in the last practice of Exercise 1.

Methods for Creating Stored Procedures

SQL Server provides several methods that you can use to create a stored procedure: the Transact-SQL CREATE PROCEDURE statement, SQL-DMO (using the StoredProcedure object), the console tree in Enterprise Manager, and the Create Stored Procedure Wizard (which you can access through Enterprise Manager).

NOTE

Many of the commands explored here and in other chapters are accessible through the SQL-DMO API. An exploration of SQL-DMO is beyond the scope of this training kit, however. Refer to SQL Server Books Online for more information about SQL-DMO.

The CREATE PROCEDURE Statement

You can use the CREATE PROCEDURE statement or the shortened version of the statement, CREATE PROC, to create a stored procedure in Query Analyzer or in a command-prompt tool such as osql. When using CREATE PROC, you can perform the following tasks:

Specify grouped stored procedures
Define input and output parameters, their data types, and their default values

When input and output parameters are defined, they are always preceded by the "at" sign (@), followed by the parameter name and then a data type designation. Output parameters must include the OUTPUT keyword to distinguish them from input parameters.

Use return codes to display information about the success or failure of a task
Control whether an execution plan should be cached for the procedure
Encrypt the stored procedure's content for security
Control execution behavior for a replication subscriber
Specify the actions that the stored procedure should take when executed

NOTE

Lesson 3 explores input and output parameters, programming actions that a stored procedure should take when executed, and success or failure result sets. For additional syntax details concerning CREATE PROC, refer to the Transact-SQL reference in SQL Server Books Online.

Providing a Stored Procedure with Context

With the exception of temporary stored procedures, a stored procedure is always created in the current database. Therefore, you should always specify the current database by using the USE database_name statement followed by the GO batch command before creating a stored procedure. You can also use the Change Database drop-down list box in Query Analyzer to select the current database.

The following script selects the Pubs database in the first batch and then creates a procedure named ListAuthorNames, which dbo owns:

 USE Pubs GO CREATE PROCEDURE [dbo].[ListAuthorNames] AS SELECT [au_fname], [au_lname] FROM [pubs].[dbo].[authors]

Notice that the procedure name is fully qualified in the example. A fully qualified stored procedure name includes the procedure's owner (in this case, dbo) and the name of the procedure, ListAuthorNames. Specify dbo as the owner if you want to ensure that the task in the stored procedure will run regardless of table ownership in the database. The database name is not part of a fully qualified stored procedure name when using the CREATE PROCEDURE statement.

Creating Temporary Stored Procedures

To create a local temporary stored procedure, append the procedure name with # when you create it. This pound sign instructs SQL Server to create the procedure in TempDB. To create a global, temporary stored procedure, append the procedure name with ## when it is created. This double pound sign instructs SQL Server to create the procedure in TempDB. SQL Server ignores the current database when creating a temporary stored procedure. By definition, a temporary stored procedure can only exist in TempDB. To create a temporary stored procedure directly in TempDB that is not a local or global temporary stored procedure, make TempDB the current database and then create the procedure. The following examples create a local temporary stored procedure, a global temporary stored procedure, and a stored procedure directly in TempDB:

 --create a local temporary stored procedure. CREATE PROCEDURE #localtemp AS SELECT * from [pubs].[dbo].[authors] GO --create a global temporary stored procedure. CREATE PROCEDURE ##globaltemp AS SELECT * from [pubs].[dbo].[authors] GO --create a temporary stored procedure that is local to tempdb. USE TEMPDB GO CREATE PROCEDURE directtemp AS SELECT * from [pubs].[dbo].[authors] GO

Fully qualified database names are specified in the SELECT statements. If the procedure is not executed in the context of a specific database and the Transact-SQL commands in the stored procedure are database-specific, then fully qualified database names ensure that the proper database is referenced.

In the third example, when creating a temporary stored procedure directly in TempDB, you must make TempDB the current database (USE TempDB) before executing it, or you must fully qualify its name ([TempDB].[dbo].[directtemp]). Like system stored procedures in the Master database, local and global temporary stored procedures are available for execution using their short names (regardless of the current database).

Grouping, Caching, and Encrypting Stored Procedures

Stored procedures can be logically tied together by grouping them upon creation. This technique is useful for stored procedures that should be administered as a single unit and are used in a single application. To group stored procedures, you assign each procedure in the group the same name and append the name with a semicolon and a unique number. For example, naming the following two stored procedures GroupedProc;1 and GroupedProc;2 on creation logically groups them together. When you view the contents of GroupedProc, you will see the code for both GroupedProc;1 and GroupedProc;2.

By default, a stored procedure's execution plan is cached the first time it is executed, and it isn't cached again until the server is restarted or until an underlying table used by the stored procedure changes. For performance reasons, you might not want to cache an execution plan for a stored procedure. For example, when a stored procedure's parameters vary considerably from one execution to the next, caching an execution plan is counterproductive. To cause a stored procedure to be recompiled every time it is executed, add the WITH RECOMPILE keywords when the stored procedure is created. You can also force a recompile by using the sp_recompile stored procedure or by specifying WITH RECOMPILE when the procedure is executed.

Encrypting a stored procedure protects its contents from being viewed. To encrypt a stored procedure, use the WITH ENCRYPTION keywords when creating the procedure. For example, the following code creates an encrypted procedure named Protected:

 USE Pubs GO CREATE PROC [dbo].[protected] WITH ENCRYPTION AS SELECT [au_fname], [au_lname] FROM [pubs].[dbo].[authors]

The WITH ENCRYPTION keywords encrypt the procedure's text column in the SysComments table. A simple way to determine whether a procedure is encrypted is by using the OBJECTPROPERTY function

 --check if stored procedure is encrypted and if so, return 1 for IsEncrypted SELECT OBJECTPROPERTY(object_id('protected'), 'IsEncrypted')

or by calling the procedure with sp_helptext:

 --if the stored procedure is encrypted, return "The object comments have been encrypted." EXEC sp_helptext protected

NOTE

An encrypted stored procedure cannot be replicated. After a stored procedure is encrypted, SQL Server decrypts it for execution. However, its definition cannot be decrypted for viewing by anyone, including the owner of the stored procedure. Therefore, make sure to place an unencrypted version of the stored procedure definition in a secure location. If the procedure needs to be modified, edit the contents of the unencrypted stored procedure and save it to the secure location. Then, use the ALTER PROCEDURE statement with encryption to modify the existing encrypted stored procedure. You will learn about the ALTER PROCEDURE statement later in this lesson.

Enterprise Manager

You can create a stored procedure directly in Enterprise Manager. To accomplish this task, expand the console tree for your server and then expand the database where a stored procedure should be created. Right-click the Stored Procedure node, and then click New Stored Procedure. When the Stored Procedure Properties - New Stored Procedure dialog box appears, enter the contents of the stored procedure. Figure 8.1 shows the Stored Procedure Properties - New Stored Procedure dialog box that contains the code from a previous example.

figure 8.1-the general tab of the stored procedure properties - new stored procedure dialog box for a new stored procedure.

Figure 8.1 The General tab of the Stored Procedure Properties - New Stored Procedure dialog box for a new stored procedure.

You can also check the syntax of the stored procedure before creating it and save a template that will always appear when you create a new stored procedure by using Enterprise Manager. Once the procedure is created, you can open the properties of the procedure and configure permissions. By default, the stored procedure's owner and sysadmins have full permission to the stored procedure.

Templates are useful because they provide a framework for creating consistent documentation for stored procedures. Typically, text is added to the header of the template that describes how each stored procedure should be documented.

Create Stored Procedure Wizard

The Create Stored Procedure Wizard walks you through the steps necessary to create a new stored procedure. You can access the Wizard by selecting Wizards from the Tools menu. In the Select Wizard window, expand the Database option, then select the Create Stored Procedure Wizard and click OK. From there, you complete the steps in the Wizard. Figure 8.2 shows the options on the Welcome to the Create Stored Procedure Wizard screen that you specify when you run the Create Stored Procedure Wizard.

figure 8.2-the welcome to the create stored procedure wizard screen.

Figure 8.2 The Welcome to the Create Stored Procedure Wizard screen.

The Create Stored Procedure Wizard enables you to create procedures that insert, delete, or update data in tables. To modify the stored procedure that the Wizard creates, you can edit it within the Wizard or by using other tools (such as Query Analyzer).

Creating and Adding Extended Stored Procedures

After creating an extended stored procedure, you must register it with SQL Server. Only users who have the sysadmin role can register an extended stored procedure with SQL Server. To register the extended stored procedure, you can use the sp_addextendedproc system stored procedure in Query Analyzer or use Enterprise Manager. In Enterprise Manager, expand the Master database, right-click the Extended Stored Procedures node, and then click New Extended Stored Procedure. Extended stored procedures can be added only to the Master database.

Deferred Name Resolution

When a stored procedure is created, SQL Server does not check for the existence of any objects that are referenced in it. This feature exists because it's possible that an object, such as a table referenced in the stored procedure, does not exist when the stored procedure is created. This feature is called deferred name resolution. Object verification occurs when the stored procedure is executed.

When referring to an object (such as a table) in a stored procedure, make sure to specify the owner of the object. By default, SQL Server assumes that the creator of the stored procedure is also the owner of the objects referenced in the procedure. To avoid confusion, consider specifying dbo as the owner when creating all objects (both stored procedures and objects referenced in the stored procedures).

Executing a Stored Procedure

As you have seen in previous lessons, you can run a stored procedure in Query Analyzer simply by typing its name and any required parameter values. For example, you viewed the contents of a stored procedure by typing sp_helptext and the name of the stored procedure to be viewed. The name of the stored procedure to be viewed is the parameter value.

If the stored procedure isn't the first statement in a batch, in order to run it you must precede the name of the stored procedure with the EXECUTE keyword or with the shortened version of the keyword, EXEC.

Calling a Stored Procedure for Execution

When you specify the procedure name, the name can be fully qualified, such as [database_name].[owner].[procedure_name]. Or, if you make the database containing the stored procedure the current database (USE database_name), then you can execute the procedure by specifying [owner].[procedure_name]. If the 0000000procedure name is unique in the active database, you can simply specify[ procedure_name].

NOTE

The identifiers ( [ ] ) that appear in an object name are included here for clarity and are used in the examples even when the object names do not violate the rules for identifiers. Refer to Chapter 2, "Using Transact-SQL on a SQL Server Database" (Lesson 3) for details.

Fully qualified names are not necessary when executing system stored procedures that have an sp_ prefix, local temporary stored procedures, or global temporary stored procedures. SQL Server will search the Master database for any stored procedures that have an sp_ prefix where dbo is the owner. To avoid confusion, do not name local stored procedures with the same names as stored procedures located in the Master database. If you do name a local stored procedure with the same name as a system stored procedure, make sure to append the procedure name with an owner other than dbo. SQL Server does not automatically search the Master database for extended stored procedures. Therefore, either fully qualify the name of an extended stored procedure or change the active database to the location of the extended stored procedure.

Specifying Parameters and Their Values

If a stored procedure requires parameter values, you must specify them when executing the procedure. When input and output parameters are defined, they are preceded by the "at" sign (@), followed by the parameter name and the data type designation. When they are called for execution, you must include a value for the parameter (and optionally, the parameter name). The next two examples run the au_info stored procedure in the Pubs database with two parameters: @lastname and @firstname:

 --call the stored procedure with the parameter values. USE Pubs GO EXECUTE au_info Green, Marjorie --call the stored procedure with parameter names and values. USE Pubs GO EXECUTE au_info @lastname = 'Green', @firstname = 'Marjorie'

In the first example, the parameter values were specified but the parameter names were not. If the values are specified without their corresponding names, the values must be called in the same order as they were specified when the procedure was created. In the second example, the parameter names and values were specified. When both the names and the values are specified, they can be listed in any order. Parameters assigned default values when a stored procedure is created might run successfully without specifying a value upon execution.

The following list shows some of the optional syntax available when executing stored procedures:

A return code variable of integer data type to store values returned from the stored procedure

The RETURN keyword with an integer value (or values) must be specified in the stored procedure for this optional variable to work.

A semicolon followed by a group number

For grouped stored procedures, you can either execute all stored procedures in the group by simply specifying the stored procedure name, or you can include a number to select which procedure in the group you wish to execute. For example, if you create two stored procedures named GroupedProc;1 and GroupedProc;2, you can run both of the stored procedures by typing EXEC groupedproc. Or, you can run procedure 2 by typing EXEC groupedproc;2.

If parameter names are defined in grouped stored procedures, each parameter name must be unique to the group. Parameter values are then specified in grouped stored procedures in the same way that they are specified when running individual stored procedures.

Variables to hold the parameters defined in the stored procedure

Variables are defined by using the DECLARE keyword before using EXECUTE. You will work with variables in Exercise 3.

NOTE

For additional syntax details about EXECUTE, refer to the Transact-SQL reference in SQL Server Books Online.

Executing Stored Procedures when SQL Server Starts

For performance, administration, or background task completion, you can mark stored procedures to run when SQL Server starts by using the sp_procoption stored procedure. This procedure accepts three parameters: @ProcName, @OptionName, and @OptionValue. The following statement configures a procedure named AutoStart to start automatically:

 USE Master GO EXECUTE sp_procoption @procname = autostart, @optionname = startup, @optionvalue = true

Only procedures owned by dbo and located in the Master database can be configured to start automatically. To automatically start procedures in other databases, you can call them from a procedure located in the Master database that is configured to start automatically. Calling one procedure from another is called nesting.

You can configure a stored procedure to start automatically by using Enterprise Manager. Access the Master database, then click the Stored Procedures node. Select a stored procedure that you wish to start automatically, and then right-click it. On the properties dialog box of the stored procedure, select the Execute Whenever SQL Server Starts check box.

To determine whether a procedure is set to start automatically, run the OBJECTPROPERTY function and check the ExecIsStartup property. For example, the following code checks to determine whether the AutoStart stored procedure is configured to start automatically:

 USE Master --determine whether a stored procedure is set to start automatically  SELECT OBJECTPROPERTY(object_id('autostart'), 'ExecIsStartup')

To disable stored procedures from starting automatically, you can run the sp_configure stored procedure. The following statement configures SQL Server so that stored procedures flagged to start automatically will not be started the next time SQL Server is started:

 EXECUTE sp_configure  @configname = 'scan for startup procs', @configvalue = 0 RECONFIGURE GO

Modifying Stored Procedures

You can use the ALTER PROCEDURE statement or the shortened version of this statement, ALTER PROC, to modify the contents of a user-defined stored procedure by using Query Analyzer or a command-prompt tool such as osql. The ALTER PROCEDURE syntax is nearly identical to the CREATE PROCEDURE syntax.

The value of using ALTER PROCEDURE rather than deleting the procedure and recreating it is that ALTER PROCEDURE retains most of the procedure's properties, such as its object ID, the permissions set for the procedure, and its startup flag. To retain encryption or recompile settings, you must specify them (WITH ENCRYPTION and/or WITH RECOMPILE keywords) when running the ALTER PROCEDURE statement.

You can use Enterprise Manager and Query Analyzer to alter user-defined stored procedures. In Enterprise Manager, right-click a user-defined stored procedure and then click Properties. In the Stored Procedure Properties dialog box, modify the stored procedure statements appearing in the Text box, and then click OK. Using Query Analyzer, right-click a user-defined stored procedure and click Edit or point to Script Object to New Window As, and then click Alter. After altering the text, execute the stored procedure.

To modify the name of a user-defined stored procedure, use the sp_rename stored procedure. The following statement renames the ByRoyalty stored procedure to RoyaltyByAuthorID:

 USE PUBS GO EXECUTE sp_rename @objname = 'byroyalty', @newname = 'RoyaltyByAuthorID',  @objtype = 'object'

User-defined stored procedures can be renamed in Enterprise Manager by right-clicking the stored procedure name and clicking Rename.

You should exercise caution when renaming stored procedures and other objects (such as tables). Stored procedures can be nested. If a call is made to a renamed object, the original stored procedure will be incapable of locating the object.

Deleting Stored Procedures

You can use the DROP PROCEDURE statement or the shortened version of this statement, DROP PROC, to delete a user-defined stored procedure, several procedures at once, or a set of grouped procedures. The following statement drops two procedures in the Pubs database: Procedure01 and Procedure02:

 USE pubs GO DROP PROCEDURE procedure01, procedure02

Notice in the example that Pubs was made the current database. You cannot specify the database name when specifying a procedure to drop. The fully qualified name of the procedure is [owner].[ procedure_name]. If you have created a user-defined system stored procedure (sp_ prefix), the DROP PROCEDURE statement will search the current database. If it doesn't find a user-defined system stored procedure of the same name, it will search the Master database.

To drop a group of stored procedures, specify the name of the procedure. You cannot delete part of a grouped procedure by using DROP PROCEDURE. For example, if you have a set of grouped procedures named GroupedProc containing GroupedProc;1 and GroupedProc;2, you cannot drop GroupedProc;1 without also dropping GroupedProc;2. If you need to delete part of a grouped procedure, drop the grouped procedure and re-create it.

Before dropping a stored procedure, you should check whether other database objects depend on the stored procedure you are dropping. Use the sp_depends system stored procedure to check for dependent objects. You need to be concerned only with the objects that depend on the stored procedure you intend to delete.

TIP

You can create, execute, alter, and drop user-defined stored procedures in Query Analyzer by right-clicking the stored procedure and pointing to any of the Script Object To... options. To generate the script in Query Analyzer, click Script Object To New Window.

Exercise 2: Working With Stored Procedures

In this exercise, you will create a stored procedure in the Northwind database and verify that it was created. In the remaining practices for this lesson, you will execute, alter, and drop this procedure. In Exercise 3, you will create stored procedures for the BookShopDB database.

To create a stored procedure in the Northwind database

Open Query Analyzer and connect to your local server.
Close the Object Browser window if it is displayed.
Expand the Query window so that it occupies all of the workspace in Query Analyzer.
In the Editor pane of the Query window, enter and execute the following code:

 USE northwind GO CREATE PROCEDURE dbo.CustOrderHistRep  @CustomerID char(5) AS SELECT ContactName, ContactTitle FROM Customers WHERE CustomerID = @CustomerID SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE    C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID   AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName GO

The Northwind database is made the current database in the first batch. Then, a procedure named CustOrderHistRep is created and a single input parameter, @CustomerID, is defined. The input parameter can appear on the same line as the CREATE PROCEDURE line, but it appears on its own line here for clarity. A similar approach for separating the code appears below the SELECT statements. Notice that the data type for @CustomerID is set to char(5). If you query the [northwind].[dbo].[customers] table, you will see that all customer IDs are five characters long. The line that has a single keyword, AS, is the demarcation line between the procedure's creation in the SysObjects table and the text contained in the procedure and stored in the SysComments table.

Review but do not spend too much time on the SELECT statements that appear below the AS keyword.

When the query is executed, the first SELECT statement displays a person's name and title for the customer ID entered. The second SELECT statement displays all product names purchased by the customer and the total quantity (SUM) of each product. The result set returns the data grouped by product name. You might notice that multiple joins were accomplished by the WHERE clause, rather than by the FROM clause. When the procedure is modified, you will move the JOIN expressions to the FROM clause.

NOTE

You should be somewhat familiar with the code in the SELECT statements. If you need a review of the language in the SELECT statement, refer to Chapter 6, "Accessing and Modifying Data."

To view the stored procedure object in Query Analyzer

Open the Object Browser in Query Analyzer and expand the Northwind database.
Expand the Stored Procedures node.

All stored procedures created for the Northwind database appear.

Expand the dbo.CustOrderHistRep stored procedure.

Notice that the stored procedure is owned by dbo and that two nodes appear below dbo.CustOrderHistRep: Parameters and Dependencies.

Expand the Parameters node.

Notice that there are two parameters for this stored procedure: the @CustomerID parameter that you created and the built-in return code parameter, @RETURN_VALUE.

Expand the Dependencies node.

Notice that this stored procedure depends on four objects: the Orders, Products, Order Details, and Customers tables in the Northwind database. No objects depend on this stored procedure.

Switch to the Editor pane of the Query window.
On a blank line at the bottom of the Editor pane, enter and execute the following line:

 sp_depends custorderhistrep

The sp_depends stored procedure checks the CustOrderHistRep stored procedure to determine dependencies.

Notice that the Name column contains duplicate entries, but that for any duplicate entries, the row values are different.

Do not close Query Analyzer, because you will use it in the next practice.

To execute the stored procedure

On a blank line at the bottom of the Editor pane, enter and execute the following code:

 EXEC [northwind].[dbo].[custorderhistrep] @CustomerID = 'thecr'

The shortened version of the EXECUTE keyword, EXEC, is used to run the stored procedure, CustOrderHistRep. Notice that the fully qualified name of the procedure was specified. This specification is not necessary, but it enables you to run the procedure without first making sure that the current database is Northwind.

Two result sets are returned. The first result set (contact name and contact title) appears in the upper portion of the Results pane. The second result set (product name and product totals) appears in the bottom portion of the Results pane.

In the previous example, EXEC was specified to run the stored procedure. Was it necessary to use this keyword?
Find the dbo.CustOrderHistRep stored procedure in the Object Browser and right-click it.
From the context menu, point to Script Object to New Windows As and then click Execute.

Query Analyzer loads a new page into the Query Window editor, and the EXECUTE statement for dbo.CustOrderHistRep appears. Notice that two variables are declared: @RC and @CustomerID. The first variable is used to hold any return codes that are part of the procedure, and the second variable holds the value of the input parameter, @CustomerID. Notice that in the EXEC statement, @RC is equal to the stored procedure. This relationship captures any return codes from the procedure and places them into @RC. In Lesson 3, you will learn more about declaring variables when executing a stored procedure and setting the return code parameter equal to the stored procedure.

In the Editor pane, click the mouse at the end of the EXEC statement so that a blinking cursor appears after the word @CustomerID. Append the following line to the end of the line: = 'thecr'. The EXEC statement should look like the following:

 EXEC @RC = [Northwind].[dbo].[CustOrderHistRep] @CustomerID = 'thecr'

Execute the query.

The query returns the same result set observed previously in this practice.

Close the new page created in the Query window, but do not close Query Analyzer or the original page that appears in the Editor pane.

A Query Analyzer message appears, asking you whether you want to save the changes.

Click No.

The original page in the Editor pane appears.

To modify the stored procedure

In the Editor pane of the Query window, locate the following code:

 USE Northwind GO CREATE PROCEDURE dbo.CustOrderHistRep  @CustomerID char(5) AS SELECT ContactName, ContactTitle FROM Customers WHERE CustomerID = @CustomerID SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE    C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID   AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName GO

Change the CREATE keyword to ALTER.

When the text in the procedure is changed, the ALTER keyword will enable the stored procedure to be changed without losing any properties configured for the object.

You want the order quantity to be listed in Descending order. Add the following statement below the GROUP BY ProductName statement:

 ORDER BY Total DESC

You have also decided to move the table joins from the WHERE clause to the FROM clause. The final form of the code should be similar to the following:

 USE Northwind GO ALTER PROCEDURE dbo.CustOrderHistRep @CustomerID char(5) AS SELECT ContactName, ContactTitle FROM Customers WHERE CustomerID = @CustomerID SELECT ProductName, Total=SUM(Quantity) FROM Products P INNER JOIN [Order Details] OD   ON P.ProductID = OD.ProductID JOIN Orders O   ON OD.OrderID = O.OrderID JOIN Customers C   ON O.CustomerID = C.CustomerID WHERE C.CustomerID = @CustomerID GROUP BY ProductName ORDER BY Total DESC GO

Execute the query.
To verify that the changes were made, go to the bottom of the Editor pane, press ENTER, then enter and execute the following statement:

 sp_helptext custorderhistrep

The stored procedure text appears on the Grids tab in the Results pane.

Leave the Query Analyzer open for the next exercise.

To delete the stored procedure

Go to the bottom of the Editor pane, press ENTER, then enter and execute the following statement:

 DROP PROCEDURE dbo.custorderhistrep

The stored procedure is deleted from the Northwind database.

Use the Object Browser in Query Analyzer or Enterprise Manager to verify that the stored procedure was removed.
Close the Query Analyzer.

You are prompted to save the script.

If you wish to save this script, press Yes. Otherwise, press No.

Lesson Summary

The CREATE PROCEDURE keywords are used to create procedures that are stored in a database. Procedures are created using graphical tools such as Enterprise Manager or command-prompt tools such as osql. Upon creation, the name of the procedure appears in SysObjects, and the procedure's content is stored in SysComments. When a procedure is created, it can be grouped, include return codes, require input parameters, supply default values for parameters, specify output parameters, be encrypted, or specify whether an execution plan should be created and whether the procedure should execute on a replication subscriber. The fundamental reason for creating a procedure is to complete a task when the procedure is executed. The EXECUTE keyword is used to run a procedure. A procedure can be run manually or flagged so that it starts when SQL Server starts. If a procedure requires modification, it is more efficient to use the ALTER PROCEDURE statement instead of deleting a procedure and recreating it. ALTER PROCEDURE maintains most of the original stored procedure object's properties. Deleting a stored procedure is accomplished by using the DROP PROCEDURE statement. If a user-defined system stored procedure is not located in the current database, SQL Server will search the Master database to delete the procedure.