SQL Server Stored Procedure Basics

 

I. Concept of Stored Procedure

A stored procedure is a set of pre-compiled SQL statements and optional control flow statements. It is stored in a database and can be executed by an application through one call, it also allows users to declare variables, conditional executions, and other powerful programming functions.

SQL Server has two types of stored procedures: stored procedures provided by the system and custom stored procedures.

Using Stored Procedures has the following advantages:

1. You can execute a series of SQL statements in a single stored procedure.

2. You can reference other stored procedures from your stored procedures, which simplifies a series of complex statements.

3. When a stored procedure is created, it is compiled on the server. Therefore, it runs faster than a single SQL statement and reduces the burden of network communication.

4. Higher security. Use parameters to avoid SQL injection.

Ii. Create a stored procedure

Before using SQL statements to create a stored procedure, consider the following:

1. The create procedure statement and other SQL statements cannot be combined into a single batch.

2. stored procedures can be nested. the maximum depth of nesting cannot exceed 32 layers.

3. The stored procedure creation permission belongs to the database owner by default. The owner can grant this permission to other users.

4. A stored procedure is a database object and its name must comply with the identifier rules.

5. You can only create a stored procedure in the current database.

6. the maximum size of a stored procedure is 128 M.

The syntax for creating a stored procedure is as follows:

Code

 Create procedure stored PROCEDURE name

(

Parameter 1 parameter type = parameter value parameter direction

Parameter 2 parameter type = parameter value parameter direction

)

AS

Begin

Stored Procedure body

RETURN

End

 

The following example shows how to create a stored procedure:

Code

 After the USE NorthwindGO/* stored procedure is created, its name is stored in the system table sysobjects, and its source code is stored in the system table syscomments. Therefore, before creating the stored procedure, check whether the stored procedure has been created in the system. If yes, delete the stored procedure first. */If Exists (select * from sysobjects where name = 'myprocessure 'and type = 'P') Drop procedure myProcedure; GO Create Proc myProcedure (@ SupplierID_2 int, @ CategoryID_3 int, @ ProductName_1 nvarchar (40) = 'none', -- the default value of this parameter is 'none' @ ProductName_2 nvarchar (40) output) AS Begin Insert into Products (ProductName, SupplierID, CategoryID) values (@ ProductName_1, @ SupplierID_2, @ CategoryID_3); select @ ProductName_2 = ProductName from products where SupplierID = @ SupplierID_1; endGO

 

Note: 1. Use SET to assign values to parameters in the statement body.

2. Parameters of all variables in the stored procedure must use "@" as the start character;

3. variables can be declared in the stored procedure and can be cyclically operated as follows:

Code

 Create    procedure   BatchAddClass

As

Begin

      Declare    @count    int

      Set   @count=10

      While   @count>0

           Begin

                Insert into  tbClass(ClassName) Values(@count)

                Set   @count=@count-1

           End

      Return

End

The preceding statement inserts 10 records into the tbClass table in batches;

 

Iii. Execution of Stored Procedures

You can run the EXECUTE Command to directly EXECUTE the stored procedure. The following uses an example to EXECUTE the stored procedure:

 

 Declare @ product nvarchar (40)

Exec myProcedure 1, 1001, @ product output

Select 'product name' = @ product

Go

 

You can use the system stored procedure to view the stored procedure you created. The system stored procedure and syntax are as follows:

Sp_help: displays the parameters and data types of stored procedures.

Sp_help [name]

The parameter name is the name of the stored procedure to be viewed.

Sp_helptext: displays the source code of a stored procedure.

Sp_helptext [name]

The parameter name is the name of the stored procedure to be viewed.

Sp_depends: used to display database objects related to stored procedures

Sp_depends [name]

The parameter name is the name of the stored procedure for viewing the dependency.

Sp_stored_procedures: Used to return the list of stored procedures in the current database

 

4. Modify the Stored Procedure

The stored procedure can be changed based on user requirements or the definition of the base table. The alter procedure statement can be used to change the previously created PROCEDURE by executing the create procedure statement without changing the permissions or affecting the stored PROCEDURE or trigger. The method for modifying a stored Procedure is the same as that for creating a stored Procedure. You only need to change the Create Procedure of the stored Procedure to Alter Procedure.

 

5. Rename and delete stored procedures

1. Rename the Stored Procedure

To modify the name of a stored procedure, use the system stored procedure sp_rename. The syntax format is as follows:

Sp_rename original stored procedure name, new stored procedure name

You can also modify the name of a stored procedure through the Enterprise Manager.

 

2. delete a stored procedure

You can use the DROP command to delete stored procedures. The DROP command can delete one or more stored procedures or stored procedure groups from the current database. The syntax is as follows:

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

Of course, you can also easily delete stored procedures using the Enterprise Manager.

 

6. Re-compile the Stored Procedure

After we use a stored procedure, we may have to add data columns to the table or add new indexes to the table for some reason, thus changing the logical structure of the database. In this case, you need to re-compile the stored procedure. SQL Server provides three methods to re-compile the stored procedure:

1. Set re-compilation when creating a stored procedure

Syntax format:

 CREATE  PROCEDURE   procedure_name    

WITH   RECOMPILE    AS   sql_statement

2. Set re-compilation when executing the Stored Procedure

Syntax format:

 EXECUTE  procedure_name  WITH  RECOMPILE

 

3. Set re-compilation by using the system stored procedure

Syntax format

  EXEC  sp_recompile  OBJECT

 

7. system stored procedures and Extended Stored Procedures

1. system stored procedures

The system stored procedures are stored in the master database and prefixed with sp _. They are mainly used to obtain information from the system table and help the system administrator manage SQL Server, it is convenient for users to view database objects. For example, sp_help, sp_helptext, and sp_helptext, which are used to view the object information of the data warehouse.

 

2. extended storage process:

The extended stored procedure is prefixed with xp _. It is part of the open data service layer of the relational database engine. It enables users to dynamically connect to the library (DLL) the functions contained in the file implement the logic, and thus extend the functions of the Transact-SQL statement. You can call these functions from the Transact-SQL statement as you call the Transact-SQL process.

For example, the stored procedure xp_cmdshell is used to execute a specified command string for an operating system shell and return any output as text.

                use master

               exec   xp_cmdshell   'dir *.exe'   

 

The execution result returns the text information of the file in the system directory.