Creating Stored Procedures

The ClassRecords database captures the relationship between Students, Teachers, and Classes. For example, one of the queries you wrote in Chapter 12 selected the StudentIDs of students who had Marsha Franklin for a teacher.

SELECT StudentID

FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherID

WHERE Teachers.LastName='Franklin' AND Teachers.FirstName=,Marsha'

In the next sections, you'll see several ways to turn the query into a stored procedure named qryStudentsOfMarshaFranklin.

Note When you first created it in Chapter 12, the ClassRecords database did not contain the stored procedures that you'll use in this or subsequent chapters, so you need to follow one of the procedures discussed next to create them. Alternatively, you can re-create the entire database (which destroys any changes you might have made) using the CreateClassRecords2.sql file in the Chl4 folder—which does include the stored procedures used in this chapter.

Creating Stored Procedures with SQL Server Query Analyzer

To create stored procedures with SQL Query Analyzer:

1. Open the SQL Server Enterprise Manager application. Click Start ® Programs ® Microsoft SQL Server ® Enterprise Manager.

2. Select the server where you installed the ClassRecords database, and expand the Databases item.

3. Select the ClassRecords database item.

4. Click the Tools menu and then select SQL Query Analyzer.

5. If the Object Browser pane is not visible, click the Object Browser button on the toolbar (see Figures 14.1 and 14.2) to display it.

Figure 14.1: SQL Query Analyzer toolbar with the Object Browser button pressed

Figure 14.2: SQL Query Analyzer with the Object Browser open

Warning Make sure you select the ClassRecords database from the drop-down list on the toolbar before you continue.

6. Click File ® New and select Create Procedure from the New dialog (see Figure 14.3).

7. Click OK. You'll see a second New dialog. Select the Create Procedure Basic Template item (see Figure 14.4).

Figure 14.3: SQL Query Analyzer New dialog
Figure 14.4: SQL Query Analyzer New dialog template selection 8. Click OK. The template appears in the query window—the right pane (see Listing 14.1). Listing 14.1: SQL Server 2000 Create Procedure Basic Template

— Create procedure basic template

— creating the stored procedure IF EXISTS (SELECT name FROM sysobjects

WHERE name = N'<procedure name, sysname, proc test>' AND type = 'P') DROP PROCEDURE <procedure name, sysname, proc test>

CREATE PROCEDURE <procedure_name, sysname, proc_test>

<@paraml, sysname, @pl> <datatype for paraml, , int> = <default value for paraml, , 0>,

<@param2, sysname, @p2> <datatype for param2, , int> = <default value for param2, , 0>

— example code to execute the stored procedure

— This code is not part of the stored procedure.

EXECUTE <procedure name, sysname, proc test> <value for paraml, , 1>, <value for param2, , 2>

Another way to insert the basic template is to click Edit ® Insert Template and then select the Create Procedure Template from the Insert Template dialog. You still need to select the type of template, in this case, Create Procedure Basic Template, from the second Insert Template screen.

Note Look at the procedure template for a second. It doesn't look much like the standard SQL

statements you saw in Chapter 12. That's because it consists largely of DDL—Data Definition Language commands. But you don't need to do much to complete the template. The if exists test drops (deletes) any existing stored procedure of the same name, if such a procedure already exists.

To replace the procedure_name, in the template select a name for the procedure. Fill in the template with the SQL statement as follows:

— Create procedure basic template

— creating the stored procedure IF EXISTS (SELECT name FROM sysobjects

WHERE name = N'qryStudentsOfMarshaFranklin'

AND type = 'P') DROP PROCEDURE qryStudentsOfMarshaFranklin

SQL Server handles both ASCII and Unicode strings. The n in front of the query name specifies that the string following it is a Unicode string. Data type names follow the same rule, although with slightly different syntax. For example, the char (character) data type has a corresponding nchar (Unicode character) data type.

CREATE PROCEDURE QryStudentsOfMarshaFranklin AS

SELECT StudentID

FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherlD

WHERE Teachers.LastName='Franklin' AND Teachers.FirstName='Marsha'

You replace the text <procedure_name, sysname, proc_test> with the name of your new stored procedure, wherever it appears. Next, because you don't have any parameters to this stored procedure, you can delete the first part of the create procedure statement. Delete the highlighted lines below:

CREATE PROCEDURE <procedure_name, sysname, proc_test> <@param1, sysname, @p1> <datatype_for_param1, , int> <default_value_for_param1, , 0>,

<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>

Note that you should delete the text that shows how to execute the stored procedure.

— example code to execute the store procedure

EXECUTE <procedure name, sysname, proc test> <value for parami, , 1>,

Finally, replace the text select @p1, @p2 with the text of your stored procedure:

SELECT StudentiD

FROM TeacherStudent iNNER JOiN Teachers ON TeacherStudent.TeacheriD=Teachers.TeacheriD

WHERE Teachers.LastName='Franklin' AND Teachers.FirstName='Marsha'

Run the text in the Query Analyzer window by clicking the Run button on the toolbar or pressing F5. Running the text creates the stored procedure. Error messages appear in the bottom pane. If SQL Server was able to create the procedure, the bottom pane will read, "The command(s) completed successfully."

You should check. Expand the Stored Procedures item under the classRecords database item in the Object Explorer. You should see an item titled dbo.qryStudentsOfMarshaFranklin. The dbo portion stands for "Database Owner," meaning that the owner of the database also owns this item.

Creating Stored Procedures with Enterprise Manager

I find it simplest to create most procedures with Enterprise Manager, primarily because it does some of the work for you.

1. Open the SQL Server Enterprise Manager application. Click Start ® Programs ® Microsoft SQL Server ® Enterprise Manager.

2. Select the server where you installed the ClassRecords database and expand the Databases item.

3. Expand the ClassRecords database item.

4. Right-click the Stored Procedures item and select New ® Stored Procedure from the context menu. You'll see the Stored Procedure Properties dialog (see Figure 14.5).

■tturrd FVmr-iliirr Prufirrln-fc - Nra il iH-rd ftrut rndurr u™BI j

di-TOI

Tirf

■ eaTE PflO ;niJ: 7 :..--.i;7h||KK;i\LLiUkb

-

T

Figure 14.5: SQL Server Enterprise Manager Stored Procedure Properties dialog

5. The dialog box already contains a little bit of templated text. You'll probably recognize it as the second half of the template text inserted by the Query Analyzer application. Overwrite the [owner].[procedure name] portion of the template with your new stored procedure name.

6. Place your cursor after the word as and insert the select query for this procedure. After doing this, you should have this text in the Text field on the Stored Procedure Properties dialog:

CREATE PROCEDURE qryStudentsOfMarshaFranklin AS SELECT StudentID

FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherlD WHERE Teachers.LastName='Franklin' AND Teachers.FirstName='Marsha'

7. Click the Check Syntax button. The dialog warns you if the stored procedure contains any errors, such as misnamed database objects (tables, fields, and so on) or misspelled keywords. If there are no errors, you'll see a Syntax Check Successful message.

8. Click the OK button to save the stored procedure. If you followed the preceding procedure, you'll get an error because the procedure already exists. The warning can save you from overwriting an existing procedure, but if you want to overwrite the existing version, here's a trick: Change the create procedure text to alter procedure. Now you can click the OK button to save your changes.

Note SQL Server Enterprise Manager has a wizard that can help you build insert, update, and delete queries, as well as views, databases, indexes, logins, full-text indexes, and other database objects. The visual query builder in SQL Server is very similar to the one in the Enterprise version of Visual Studio, so if you don't have the Enterprise version of Visual Studio, you can use the one in Enterprise Manager. Unfortunately, Enterprise Manager Wizard doesn't help you build select queries—you'll need to write code to do that. You can access the Enterprise Manager Wizards via the Tools menu.

Creating Stored Procedures with Server Explorer (VS Enterprise Version Only)

This procedure works only with the Enterprise version of Visual Studio. The Professional version lets you browse database items but does not let you create them.

1. Open Visual Studio and show the Server Explorer. Expand the Servers item and select the server that contains the instance of SQL Server containing the ClassRecords database. If the connection isn't already there, you will need to create it as described in Chapter 12.

2. Expand the SQL Servers item and select the SQL Server instance that contains the ClassRecords database. Expand the ClassRecords database item, then right-click the Stored Procedures item and select New Stored Procedure from the pop-up menu.

Following step 2, you'll see yet another template. By now, this should look familiar, so I won't go through the procedure in detail. You can enter the query by hand, but the big advantage of the Enterprise version in this case is that you have access to the Query Builder, which lets you build queries by selecting fields from a visual view of the tables. To access the Query Builder, right-click in the editor window and select the Insert SQL item from the pop-up menu. For complex queries with many joins, the Query Builder can be a great timesaver, especially at first, but for simple queries, there's no big advantage to using the Query Builder.

Was this article helpful?

0 0

Post a comment