SQL Server -- Stored Procedure

I would like to explain the stored procedure in the following aspects. Some knowledge points may not be worth your attention, or I may not know some knowledge points. Thank you for your advice. If you have any questions, please feel free to advise!

Stored Procedure Concept

Advantages of Stored Procedure

Stored Procedure Interface

Parsing and compiling of Stored Procedures

Stored Procedure Security

View stored procedures

Encryption and decryption of Stored Procedures

Stored Procedure Concept

Stored Procedure is a set of SQL statements for specific functions. It is compiled and stored in the database. You can run a stored procedure by specifying the name of the stored procedure and providing parameters (with parameters.

Advantages of Stored Procedure

The advantages of using stored procedures in the development process are summarized as follows:

1: High Speed and good performance. A stored procedure is a set of compiled SQL scripts that have been parsed, compiled, and optimized by the optimizer. Calling a stored procedure can reuse its cache execution plan. This saves the CPU resources required for parsing and analysis. Time

2: stored procedures are stored on database servers, which can reduce network communication and network congestion. To call a stored procedure, you only need to store the name and parameters of the stored procedure. Therefore, you do not need to transmit long SQL statements to the SQL Server, which greatly reduces the network burden.

3: business logic encapsulation: a considerable number of business logic can be encapsulated into the stored procedure. When the business logic changes, as long as the interface remains unchanged, it is okay to modify the internal logic of the stored procedure, avoid putting business logic inCodeLayer.

4: Security parameterized stored procedures can reduce SQL injiection attacks, and improve security by checking parameters and granting object execution permissions.

Stored procedures are classified into system stored procedures, extended stored procedures, and user stored procedures (including CLR Stored Procedures) by type) temporary stored procedures (including global temporary stored procedures and local temporary stored procedures ). System stored procedures generally start with an SP prefix. Extended stored procedures are generally prefixed with XP and are named after custom stored procedures. I used to use USP as the prefix.

Stored Procedure Interface

Stored procedure parameters can be input and output parameters. First, let's take a look at the following two stored procedures. The first stored procedure has two input parameters: @ employeeid and @ employeename. The default value of @ employeeid is-1 and that of @ employeename is null.

Use myassistant;
Go
If object_id (N 'dbo. usp_getemployebyid ') is not null
Begin
Drop proc DBO. usp_getemployebyid;
End
Go
-- ===================================================== ========================================================== ==================================
-- Function: obtains employee information by employee ID.
-- Author: Kerry
-- Create Date: 2010-08-10
-- Description:
Bytes ------------------------------------------------------------------------------------------------------
-- 2010-08-13: Modify ...... add .....
-- 2010-08-14: Modify ...... add .....
-- ===================================================== ========================================================== ==================================
Create procedure DBO. usp_getemployebyid
@ Employeeid Int =-1,
@ Employeename nvarchar (30) = NULL
As
Set nocount on;
Begin
If (@ employeeid =-1 and @ employeename is null)
Begin
Print 'enter employee ID or user name ';
End
If @ employeeid =-1
Select * From DBO. Employee where employeename = @ employeename;
Else
Select * From DBO. Employee where employeeid = @ employeeid;
End
Go

Code

Use myassistant;
Go
If object_id (N 'dbo. usp_addemploye ') is not null
Begin
Drop proc DBO. usp_addemploye;
End
Go
-- ===================================================== ========================================================== ======================================
-- Function: adds an employee record.
-- Author: Kerry
-- Create Date: 2010-08-10
-- Description:
Bytes -------------------------------------------------------------------------------------------------------------
-- 2010-08-13: Modify ...... add .....
-- 2010-08-14: Modify ...... add .....
-- ===================================================== ========================================================== ======================================
Create procedure DBO. usp_addemploye
@ Employeename nvarchar (30 ),
@ Sex bit = 1,
@ Department nvarchar (20 ),
@ Success nvarchar (4) Output
As
Set nocount on;
Begin try
If (@ employeename is null or Len (@ employeename) = 0)
Begin
Print ('employee name cannot be blank ');
Set @ success = 'failed ';
Return;
End
Insert into employee
Values (@ employeename, @ sex, @ Department)
If @ error = 0
Set @ success = 'success ';
End try
Begin catch
Set @ success = 'failed ';
Select
Error_number () as errornumber
, Error_severity () as errorsevertiy
, Error_state () as errorstate
, Error_line () as errorline
, Error_procedure () as errorprocedure
, Error_message () as errormessage
End catch
Go

You can call and execute stored procedures in this way, or you do not need to add parameters such as @ employeename.

Declare @ result nvarchar (4 );

Set @ result = '';
Exec DBO. usp_addemploye
@ Employeename = 'zhangfe ',
@ Sex = 1,
@ Department = 'marketing Department ',
@ Success = @ result output
Select @ result;
Go

Generally, it is best to add the architecture name when executing the stored procedure, such as DBO. usp_addemploye, to reduce unnecessary system overhead and improve performance. If no schema name is added before the stored procedure name, SQL Server first searches for the Current Database SYS schema. If no schema name is found, SQL Server searches for other schemas, finally, search for the DBO architecture.

Parsing and compiling of Stored Procedures

When creating a stored procedure, analyze and check the correctness of the syntax. If a syntax error is encountered in the process definition, an error is returned, indicating that the stored procedure fails to be created. If the syntax is correct, the stored procedure text will be stored in the SYS. SQL _modules directory view.

Select * From SYS. SQL _modules where object_id = object_id (N 'dbo. usp_getemployebyid ')

Let's take a look at an interesting small column. You can also try it. Create a stored procedure usp_gettabletest, which references the table test. The table test does not exist at all.

Create procedure usp_gettabletest
As
Begin
Select * from test;
End

No error occurs when you create the stored procedure. However, the following error occurs when you execute the stored procedure.

This is because when a stored procedure is created, it first performs a syntax check. If the syntax check is passed, it tries to parse the object name it contains, if yes, the system will also parse whether the referenced object exists. If the referenced object name does not exist, resolution is triggered when the stored procedure is executed for the first time. That is, when a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the SYS. SQL _modules directory view and checks whether the object name used in the procedure exists. This process is called delayed name resolution because the table objects referenced by the stored procedure do not need to exist when the stored procedure is created, but only need to exist when the stored procedure is executed.

Note:

You can use delayed name resolution only when the referenced table object does not exist. All other objects must exist in the stored procedure. For example, when you reference an existing table in the stored procedure, you cannot list columns that do not exist in the table.

Let's take a look at the following columns. First, create the table test (col1). Then, query the column col2 that does not exist in the Stored Procedure usp_gettabletest.

Code

Create Table Test (col1 INT );
Go
If object_id (N 'usp _ gettabletest') is not null
Begin
Drop proc usp_gettabletest;
End
Go
Create procedure usp_gettabletest
As
Begin
Select col2 from test;
End

When a stored procedure is created, the following error message is displayed.

In the parsing phase, Microsoft SQL Server 2005 also performs other verification activities (for example, checking the compatibility between column data types and variables ). If the objects referenced by the stored procedure are lost when a stored procedure is executed, the stored procedure stops executing the statement when the referenced lost object is reached. In this case, or when other errors are found in the parsing phase, an error message is returned.

If the parsing phase is successfully passed during execution, the Microsoft SQL Server Query Optimizer analyzes the statements in the stored procedure and creates an execution plan. The execution plan describes the fastest way to execute a stored procedure, based on the following information:

The amount of data in the table.

The existence and features of the table index and the distribution of data in the index column.

The comparison operator and comparison value used by the WHERE clause condition.

Whether a join exists and the keyword 'Union, 'Group by', and 'ORDER BY' exists.

After analyzing these factors in the stored procedure, the query optimizer places the execution plan in the memory. The process for analyzing stored procedures and creating execution plans is called compilation. The execution plan in the optimized memory is used to execute the query. The execution plan will reside in the memory until the SQL server or other objects need to be restarted.

If the stored procedure is subsequently executed and the existing execution plan remains in the memory, SQL Server will reuse the existing execution plan. If the execution plan is no longer in memory, a new execution plan is created.

Stored Procedure Security

Under the SA account, the following statement is executed:

Deny insert on DBO. employee to Kerry;
Grant execute on DBO. usp_addemploye to Kerry;

Then we use the Kerry account to log in and insert a data record to the table employee.

When you call the stored procedure and insert a data entry to the table employee

This security mode allows you to flexibly control the activities permitted by users.

View stored procedures

There are many ways to view stored procedures. You can select "modify stored procedure" or "Write stored procedure as" in msms to view the stored procedure. You can also query the view, or a system stored procedure to view the stored procedure you want to view

Code

Sp_help 'dbo. usp_getemployebyid' -- view the basic information of a stored procedure, such as parameters.
Sp_helptext 'dbo. usp_getemployebyid' -- view the specific Stored Procedure
Select * From SYS. SQL _modules where object_id = object_id (N 'dbo. usp_getemployebyid ') -- view the specific Stored Procedure

Encryption and decryption of Stored Procedures

Stored Procedure encryption is generally for security needs or protectionSource codeThe encryption stored procedure is generally implemented by the keyword encryption. SQL Server converts the original text of the create procedure statement to a fuzzy format. Fuzzy code output cannot be directly displayed in any directory view of SQL Server 2005. Users who do not have access to system tables or database files cannot retrieve fuzzy text. However, privileged users who can access system tables through the DAC port or who can directly access database files can use this text. In addition, users who can append a debugger to server processes can retrieve decrypted processes from the memory at runtime.

Let's encrypt the stored procedure.

Code

Use myassistant;
Go
If object_id (N 'dbo. usp_getemployebyid ') is not null
Begin
Drop proc DBO. usp_getemployebyid;
End
Go
-- ===================================================== ========================================================== ========================
-- Function: obtains employee information by employee ID.
-- Author: Kerry
-- Create Date: 2010-08-10
-- Description:
Bytes --------------------------------------------------------------------------------------------------
-- 2010-08-13: Modify ...... add .....
-- 2010-08-14: Modify ...... add .....
-- ===================================================== ========================================================== ========================
Create procedure DBO. usp_getemployebyid
@ Employeeid Int =-1,
@ Employeename nvarchar (30) = NULL
With Encryption
As
Set nocount on;
Begin
If (@ employeeid =-1 and @ employeename is null)
Begin
Print 'enter employee ID or user name ';
End
If @ employeeid =-1
Select * From DBO. Employee where employeename = @ employeename;
Else
Select * From DBO. Employee where employeeid = @ employeeid;
End
Go

After the execution, you can see the encrypted stored procedure. Its icon has a small lock, and you can no longer view the stored procedure by viewing the stored procedure above. The following error message is displayed when you use msms to view logs.

When sp_helptext is used, the system prompts that the text of the object 'dbo. usp_getemployebyid 'is encrypted.

The stored procedure created using the with encryption option in sqlserver2005 is still the same as that in sqlserver2000, and is encrypted using XOR. Unlike 2000, encrypted ciphertext cannot be found in the system table syscomments of 2005. To query the ciphertext, you must use the DAC (dedicated administrator connection) to connect to the database. If you take over the database management, some stored procedures are encrypted and you have not created any scripts for the encrypted stored procedures, are you in a hurry. Don't worry, huh, there is a stored procedure, usp_decrypt, on the Internet called Wang Chenghui translated and compiled the decryption and encryption stored procedure written by Daniel abroad. If you are interested, you can check it out. I have tested it in SQL Server 05 and can indeed decrypt the encrypted stored procedure.