Detailed description of Stored Procedures

 

7.2.1 basic knowledge of stored procedures 1. What is stored procedure

During the development of SQL Server Applications, T-SQL statements are the main programming interface used between applications and SQL Server databases. The application interacts with the SQL Server database to execute certain operations in two ways: one is the local application that records operation commands, and the application sends each command to SQL Server, and process the returned data. The other is to define a process in SQL Server, which records a series of operations. Each time the application calls this process, the operation can be completed. This process defined in SQL Server is called a stored procedure.

2. Stored Procedure Functions

Stored Procedures in SQL Server are similar to those in programming languages. They provide the following functions:

· Accept input parameters and return multiple output values.

Contains T-SQL statements for specific SQL server operations.

· Return a status code indicating the success or failure cause to call it.

A stored procedure is a set of pre-compiled Transact-SQL statements. The main body consists of standard SQL commands and SQL extensions: statement blocks, structure control commands, variables, constants, operators, expressions, and flow control. All these are combined to construct a stored procedure.

3. Advantages of Stored Procedures

Stored Procedures have the following advantages:

· Modular programming is allowed to enhance code reusability and sharing

· Using stored procedures can speed up Operation

· Using stored procedures can reduce network traffic.

· Stored procedures can be used as security mechanisms.

4. Storage Process Classification

Stored Procedures include system stored procedures, user stored procedures, temporary stored procedures, extended stored procedures, and remote stored procedures.

System stored procedures are provided by the system and can be directly executed as commands. System stored procedures can also be used as template stored procedures to guide users in writing effective stored procedures. System stored procedures are stored in the master database with the prefix SP _. The system stored procedure can be executed in any database. Appendix B of this book provides common system stored procedures.

A user stored procedure is a stored procedure created in a user database. The name is not prefixed with SP. It is mainly used in applications to complete specific tasks.

Temporary stored procedures belong to user stored procedures. If the user stored procedure is preceded by the symbol "#", the stored procedure is called a local temporary stored procedure and can only be used in one user session; if the user stored procedure is preceded by the symbol "#", this process is called a global stored procedure and can be used in all user sessions.

The extended stored procedure is a dynamic link library DLL that is executed outside the SQL server environment. Its prefix is XP _. Although these dynamic linked libraries are outside the SQL server environment, they can be loaded into the SQL Server System and executed as stored procedures.

A remote stored procedure is a stored procedure called from a remote server or a stored procedure called from a client connected to another server. It is a stored procedure on a non-local server.

The following describes how to create, modify, and delete a stored procedure.

7.2.2 create a stored procedure

You can use the wizard, Enterprise Manager, and transact-SQL commands to create a stored procedure in SQL Server.

1. Use the Wizard to create a stored procedure

To create a stored procedure using the wizard, follow these steps:

(1) Start the Enterprise Manager and expand the database directory.

(2) Select wizard from the Tools menu of Enterprise Manager.

(3) In the select wizard dialog box, expand database and click Create storage process wizard.

(4) Click OK to open the welcome dialog box for creating a stored procedure (see figure 7-6). This dialog box describes the main steps for creating a stored procedure using the create wizard.

Process.

 

Figure 7-6 welcome dialog box for creating a stored procedure figure 7-7 select a stored procedure dialog box

(5) Click Next to open the database selection dialog box (the stored procedure must be created in a database). Select the desired database mydb from the database drop-down list.

(6) Click the next button to open the "select stored procedure" dialog box. The dialog box contains four lists: 1st columns for all tables in the selected database, 2nd columns for insert check boxes, and 3rd columns for Delete, column 4th is the update check box. In this dialog box, select the table books to be included in the stored procedure and the operation insert to the table, as shown in 7-7.

(7) Click "Next" to open the "complete storage creation process" dialog box, 7-8. the dialog box displays the name and description information assigned to the stored procedure by the system. Click Edit...] button to enter the edit Stored Procedure dialog box, which can be used to modify the name of the stored procedure. The list box contains four columns of information, including column name, data type, length, and whether to select the check box. By default, all columns are selected, and the required columns are selected by clicking the check box, as shown in 7-9.

In the edit Stored Procedure dialog box, click Edit SQL ...] Button to open the "Edit Stored Procedure SQL" dialog box and edit the statements, as shown in 7-10. After editing, click OK to return to the previous dialog box.

(8) In the dialog box shown in Figure 7-8, click Finish to complete the creation of the stored procedure. A new stored procedure is formed in the selected database.

Note that the stored procedure function of the Creation wizard is limited. It is only applicable to simple operations on tables. To create a stored procedure with complex functions, use the create procedure command.

 

Figure 7-8 create a stored procedure dialog box figure 7-9 edit a stored procedure dialog box

Figure 7-10 edit Stored Procedure SQL dialog box

2. Create a stored procedure using the Enterprise Manager

To create a stored procedure using the Enterprise Manager, follow these steps:

(1) expand the database in which you want to create a stored procedure.

(2) Right-click the Stored Procedure icon and choose create stored procedure... from the shortcut menu to open the create Stored Procedure dialog box, as shown in 7-11.

Figure 7-11 create a stored procedure dialog box

(3) write the stored procedure definition in the text box of the create Stored Procedure dialog box. The check syntax button is used to check the correctness of the stored procedure syntax. After defining the stored procedure, click OK to save the stored procedure.

This method is almost identical to the stored procedure created using the transact-SQL command described below.

3. Use the transact-SQL command to create a stored procedure

The syntax structure of the stored procedure is as follows:

Create proc [edure] procedure_name [; number] [{@ parameter data_type}

[Varying] [= default] [Output] [,... n]

[With {recompile | encryption | recompile, encryption}]

[For replication] As SQL _statement [... n]

Note:

(1) procedure_name: name of the stored procedure. Its naming Rules comply with the naming rules for SQL Server identifiers. The maximum length is 128 characters.

(2) [; number] An optional integer, used to divide stored procedures with the same name into groups, so that a separate drop procedure statement can be used to cancel the statement.

(3) @ parameter one or more parameters can be declared when a stored procedure is created. The maximum number is 1024.

(4) varying is only used for cursor parameters.

(5) default value of the default parameter. It can be null or contain wildcards (% or _).

(6) Output indicates that the parameter is an output parameter. It is returned when exec [ute] is executed. It cannot be of the text type.

(7) With recompile re-compile each time a stored procedure is executed to generate a new execution plan, which cannot be used together with for replication.

(8) with encryption encrypts Stored Procedure text in the syscomments table, so that you cannot use sp_helptext to view Stored Procedure content.

(9) for replication indicates that the stored procedure cannot be executed on the subscriber and can only be executed during replication.

(10) SQL _statement is the content of the transact-SQL statement in the main part of the stored procedure.

Stored procedures can only be created in the current database, and can only be created for members with the system administrators, db_owner, or db_ddladmin roles. In the create procedure statement, you can include any number of transact-SQL statements, but you cannot use the statements for creating objects such as create default, create procedure, create rule, create trigger, and create view.

[Example 7-18] create a simple stored procedure.

Use mydb

Go

Create procedure borrowed_num

As

Select name, borrowed quantity

From Readers

Where name = 'Liu Chao'

Go

[Example 7-19] create a complex stored procedure through multi-table join query.

Use mydb

Go

Create procedure borrowed_books1

As

Select R. No., R. Name, B. Book No., K. Name, B. Borrow Period

From Readers R inner join borrowinf B

On R. No. = B. Reader No. Inner join books K

On B. Book No. = K. No.

Where name = 'Liu Chao'

7.2.3 parameters in Stored Procedures

Stored procedure parameters in SQL Server include input and output parameters. Parameters extend the functionality of SQL Server, and realize its flexibility by storing different parameter values during each execution.

1. input parameters

The input parameter is used to pass the value to the stored procedure.

[Example 7-20] use the input parameter to display the situation of someone borrowing books.

Use mydb

Go

Create procedure borrowed_books2

@ Name varchar (10)

As

Select R. No., R. Name, B. Book No., K. Name, B. Borrow Period

From Readers R inner join borrowinf B

On R. No. = B. Reader No. Inner join books K

On B. Book No. = K. No.

Where name = @ name

In this example, input parameters are used to specify different query conditions for each execution.

You can use the following methods to pass a value to a stored procedure:

(1) Pass in the value directly, for example, exec borrowed_books2 'zhanggang '.

(2) Use variables of the same type as declared. For example, exec borrowed_books2 @ Templ (here, @ Templ is a declared character type variable and has been assigned a value ).

Note: The specified input parameter values must be of the same type as they are defined, and the input parameter values must be in the same order as the parameters are declared in the stored procedure.

(3) Use the parameter name for transmission. When multiple input parameters exist, the parameters can be passed in any order. However, if you use a name for a parameter, you must use the name for all subsequent parameters.

If the value is not passed into the corresponding parameter, and the default value is not assigned to the parameter when the stored procedure is created, an error occurs when the stored procedure is executed.

[Example 7-21] modify Example 7-20 and use the default parameter.

Use mydb

Go

Create procedure borrowed_books3

@ Name varchar (10) = NULL

As

If @ name is null

Select R. No., R. Name, B. Book No., K. Name, B. Borrow Period

From Readers R inner join borrowinf B

On R. No. = B. Reader No. Inner join books K

On B. Book No. = K. No.

Else

Select R. No., R. Name, B. Book No., K. Name, B. Borrow Period

From Readers R inner join borrowinf B

On R. No. = B. Reader No. Inner join books K

On B. Book No. = K. No.

Where name = @ name

Go

Please analyze this example by yourself.

2. Output Parameters

The output parameter is used to assign the return value to the variable and pass it to the stored procedure or application that calls it. When declaring an output parameter, you must add output after the declared parameter to indicate that this parameter is an output parameter.

[Example 7-22] calculate factorial using output parameters.

Use mydb

If exists (Select name from sysobjects

Where name = 'factorial 'and type = 'P ')

Drop procedure factorial

Go

Create procedure factorial

@ In float,

@ Out float output

As

Declare @ I int

Declare @ s float

Set @ I = 1

Set @ s = 1

While @ I <= @ in

Begin

Set @ s = @ s * @ I

Set @ I = @ I + 1

End

Set @ out = @ s

In the query analyzer, run the stored procedure using the following code:

Declare @ ou float

Exec factorial 10, @ ou out

Print 'factorial: '+ Cast (@ ou as varchar (20 ))

To execute a stored procedure with an output parameter, you must declare a variable to accept the returned value (this variable is not necessarily the same as the output parameter declared during the creation of the stored procedure ), the keyword out (output) must be used after the variable ).

7.2.4 modify stored procedure 1. Use the Enterprise Manager to modify

To modify a stored procedure, you only need to find the stored procedure to be modified in the Stored Procedure object of the corresponding database. Double-click the stored procedure to open the Stored Procedure Properties window, 7-12, and directly modify the stored procedure in the window, click OK.

Figure 7-12 Stored Procedure Attributes dialog box

2. Use T-SQL commands for modifications

You can use alter procedure to modify a stored procedure. The syntax structure is as follows:

Alter proc [edure] procedure_name [; number]

[{@ Parameter data_type} [varying] [= default] [Output] [,… N]

[With {recompile | encryption | recompile, encryption}]

{For replication}

As

SQL-statement [... N]

Note: Alter procedure is similar to create procedure. The options used in the create procedure command must also be used in alter rocedure. Alter rocedure can only modify one stored procedure. If the stored procedure calls other stored procedures, the called stored procedures are not affected. Only members with db_owner and db_ddladmin permissions are allowed to execute alter rocedure. Other users are not allowed to execute the alter rocedure statement.

3. Use the query analyzer to modify

Open the query analyzer, press F8 (or click the Object Browser button on the toolbar), In the Object Browser window, open the database where the stored procedure is located, open the [Stored Procedure] folder, right-click the name of the stored procedure to be modified (such as test), and select edit in the pop-up menu (as shown in 7-13). The Stored Procedure appears in the query analyzer editing window, save the modification.

Figure 7-13 edit a stored procedure in the query Analyzer

7.2.5 run Stored Procedure 1. Compile Stored Procedure

When the stored procedure is run for the first time, SQL Server compiles and verifies the program. If an error is found, the system rejects the execution of the stored procedure.

Even if you only execute one Transact-SQL statement, you must create an execution plan that includes the index of the table rows required by the stored procedure. The execution plan is stored in the cache to complete the query tasks of the stored procedure during subsequent execution, improving the execution speed. The stored procedure is recompiled in the following cases:

(1) When SQL Server is restarted or the stored procedure is executed for the first time.

(2) After a stored procedure is modified or its referenced table index is deleted, the execution plan is re-created.

(3) When a user is using the execution plan in the buffer zone, recompile it to create the second execution plan for the second user.

(4) After the stored procedure is deleted or rebuilt, all the execution plan spare parts in the buffer zone are deleted and re-compiled automatically during execution to form a new execution plan.

2. Execution of Stored Procedures

Users with execution permission of stored procedure can execute stored procedure. In the query analyzer, you can directly enter the stored procedure name and specify the corresponding Input and Output Parameters for execution. Or execute the Execute Command. This method is also suitable for calling stored procedures in applications. Unlike a function, a stored procedure cannot directly return values using the process name or in an expression. The syntax structure of execute is as follows:

[[Exec [ute]

{[@ Return status =]

Procedure_name [; number] | @ procedure_name_var)

[[@ Parameter =] {value | @ variable [Output] | [Default]} [,… N]

[With recompile]

Note:

(1) Use the with recompile option to forcibly re-compile the stored procedure.

(2) @ return status: an integer variable used to save the returned status of the stored procedure.

7.2.6 delete stored procedures 1. Use the Enterprise Manager to delete stored procedures

Using SQL Server Enterprise Manager to delete stored procedures is a simple and effective method. Expand the database, click the Stored Procedure icon, select the process to be deleted from the Stored Procedure displayed in the window on the right, right-click the process, and click Delete in the shortcut menu that appears, deletes a stored procedure.

2. Use T-SQL commands to delete stored procedures

Use the drop procedure command to delete a stored procedure. The syntax structure is as follows:

Drop procedure {procedure} [,... n]

[Example 7-23] Delete the Stored Procedure borrowed_num created in the previous example.

Use mydb

Go

Drop procedure borrowed_num

After a stored procedure is deleted, its definitions stored in sysobjects and syscomments are also deleted. If you use the system procedure sp_helptext to view the Stored Procedure text, you will not see the definition content.

7.2.7 view stored procedure information

Using the SP helptext system stored procedure, you can view the T-SQL statements that define the stored procedure. Figure 7-14 shows how to use the query analyzer to view the definition of the stored procedure borrowed_books1.

Figure 7-14 shows the definition of the stored procedure

Use the Enterprise Manager to open the Stored Procedure Properties dialog box by following the steps described in the previous section to modify the stored procedure. You can also view the definition of the stored procedure.

If the definition of a stored procedure is encrypted, that is, the with encryption clause is used in the statement for defining or modifying the stored procedure, the stored procedure definition is stored in the syscomments table as unreadable. You cannot view the definition of a stored procedure. For example, use the following statement to modify the borrowed_books1 definition:

Use mydb

Go

Alter procedure borrowed_books1

With Encryption

As

Select R. No., R. Name, B. Book No., K. Name, B. Borrow Period

From Readers R inner join borrowinf B

On R. No. = B. Reader No. Inner join books K

On B. Book No. = K. No.

Where name = 'Liu Chao'

 

Run the preceding statement in the query analyzer. The result window displays:

The command has been successfully completed.

If the stored procedure is modified successfully, execute sp_helptext borrowed_books1, and the text defined by borrowed_books1 will not be visible. The system prompts "the object remarks are encrypted ".

Use the system stored procedure sp_help to obtain information about the creator, creation date, and parameters used by the stored procedure.

The system stored procedure sp_depends can be used to list the objects used by the stored procedure and the names of other stored procedures that call the stored procedure. This information can be used to understand which database objects will be affected when the stored procedure is deleted.