SQL Server Stored Procedure

The SQL Server Stored Procedure is a named set of transacation-SQL statements stored on the server. It is a method for encapsulating repetitive work, it supports declared variables, conditional execution, and other powerful programming functions.

SQL Server Stored Procedures have the following advantages over other database access methods:

(1) reuse. Stored procedures can be reused to reduce the workload of database developers.

(2) improve performance. The stored procedure is compiled when it is created. You do not need to re-compile the stored procedure in the future. A general SQL statement needs to be compiled every time it is executed, so the efficiency is improved by using the stored procedure.

(3) reduce network traffic. The stored procedure is stored on the server. You only need to pass the name and parameters of the stored procedure when calling the procedure. This reduces the amount of data transmitted over the network.

(4) Security. Parameterized stored procedures can prevent SQL injection attacks and apply Grant, deny, and revoke permissions to stored procedures.

SQL Server Stored Procedures are divided into three types: User-Defined stored procedures, extended stored procedures, and system stored procedures.

User-Defined stored procedures are classified into transaction-SQL and CLR.

1. The transaction-SQL stored procedure is a set of stored transaction-SQL statements that can accept and return user-supplied parameters.

2. CLR Stored Procedures refer to the reference to the. NET Framework Common Language Runtime (CLR) method, which can accept and return user-supplied parameters. They are in the. NET FrameworkProgramThe set is implemented as a public static method of the class. (This article will not be introduced)

The statement used to create the SQL server stored procedure is as follows:

Reference content is as follows:

 
 

  
  

  
Create {proc | procedure} [Schema_name.] procedure_name [; number]
 
  
  
 
  
[{@ Parameter [type_schema_name.] data_type}
 
  
  
 
  
[Varying] [=Default] [[Out [put]
 
  
  
 
  
] [, N]
 
  
  
 
  
[With <procedure_option> [, N]
 
  
  
 
  
[For replication]
 
  
  

  
As {<SQL _statement> [;] [N] | <method_specifier>}
 
  
  
 
  
[;]
 
  
  
 
  
<Procedure_option >::=
 
  
  
 
  
[Encryption]
 
  
  
 
  
[Recompile]
 
  
  
 
  
[Execute_as_clause]
 
  
  
 
  
 
 
  
  
 
  
<SQL _statement >::=
 
  
  
 
  
{[Begin] Statements [end]}
 
  
  
 
  
 
 
  
  

  
<Method_specifier >::=
 
  
  
 
  
External name assembly_name.class_name.method_name
 
  
  
 
  
 
 
 
 

[Schema_name]: name of the architecture to which the stored procedure belongs

For example:

 
  
  


   
   
Create schema yangyang8848

   
   
Go

   
   
Create proc yangyang8848.allgoods

   
   
As select * From master_goods

   
   
Go

  
  
 

Execute: exec allgoods has an error.

Run exec yangyang8848.allgoods.

[; Number]: an optional integer used to group processes with the same name. You can use a drop procedure statement to delete these grouping processes together.

For example:

  
  


   
   
Create proc S1; 1

   
   
As

   
   
Select * From master_goods

   
   
Go

   
   
Create proc S1; 2

   
   
As

   
   
Select * From master_location

   
   
Go

  
  
 

Two stored procedures have been created. They are in the same group S1. If exec S1 is executed, the stored procedure defaults to Exec S1; 1. If we want to obtain information about all data points, execute exec S1; 2. To delete a stored procedure, only drop exec S1 can be executed, and all stored procedures in the group are deleted.

[@ Parameter]: A parameter in a stored procedure. You must assign a value to the parameter when calling the stored procedure unless the default value is set during Parameter definition or the parameter is set to equal to another parameter.

SQL Server Stored Procedures have a maximum of 2100 parameters.

For example:

  
  


   
   
Create proc yangyang8848.onegoods

   
   
@ Goodscode varchar (10)

   
   
As

   
   
Select * From master_goods where goodscode = @ goodscode

   
   
Go

  
  
 

CalledCode:

 
  
  


   
   
Declare @ code varchar (10)

   
   
Set @ code ='123' 

   
   
Exec yangyang8848.onegoods @ code

  
  
 

Adding output after the parameter indicates that this parameter is an output parameter.

  
  


   
   
Create proc yangyang8848.onegoods

   
   
@ Goodscode2 varchar (10) output, @ goodscode varchar (10) ='123' 

   
   
As

   
   
Select * From master_goods where goodscode = @ goodscode

   
   
Set @ goodscode2 ='123' 

   
   
Go

  
  
 

Call method:

  
  


   
   
Declare @ vv2 varchar (10)

   
   
Exec yangyang8848.onegoods @ codeOut 

  
  
 

NOTE: If one of the two parameters in the stored procedure has a default value and the other does not, we need to put the default value behind it. Otherwise, a problem may occur ~~

Careful friends may see some differences in the preceding statements. For example, the stored procedure uses output, while the calling statement uses out. I want to tell you that the two are the same.

[Recompile]: indicates that the database engine does not cache the plan of the process and the process is compiled at runtime. If for replication is specified, this option cannot be used. Recompile cannot be specified for CLR stored procedures.

This is a very useful function object_id: returns the database object ID of the objects in the schema range.

For example, when creating a stored procedure, you can write the following code:

 
 

  
  
 
  
If object_id ('Angyang8848. onegoods') Is not null
 
  
  
 
  
Drop proc yangyang8848.onegoods
 
  
  
 
  
Go
 
  
  

  
 
 
  
  
 
  
Create proc yangyang8848.onegoods
 
  
  
 
  
@ Goodscode2 varchar (10)Out, @ Goodscode varchar (10) ='123' 
 
  
  
 
  
As
 
  
  
 
  
Select * From master_goods where goodscode = @ goodscode
 
  
  
 
  
Set @ goodscode2 ='123' 
 
  
  
 
  
Go
 
 
 

For the stored procedure above, we call the following SQL query

  
  


   
   
SelectDefinitionFromSYS. SQL _modules

   
   
WhereObject_id = object_id ('Angyang8848. onegoods');

  
  
 

We can check the results.

However, if we add [encryption] to this stored procedure, you will not be able to see any results.

 

 
 

  
  
 
  
If object_id ('Angyang8848. onegoods') Is not null
 
  
  
 
  
Drop proc yangyang8848.onegoods
 
  
  
 
  
Go
 
  
  
 
  
 
 
  
  

  
Create proc yangyang8848.onegoods
 
  
  
 
  
@ Goodscode2 varchar (10)Out, @ Goodscode varchar (10) ='123' 
 
  
  
 
  
 
 
  
  
 
  
With Encryption
 
  
  
 
  
As
 
  
  
 
  
Select * From master_goods where goodscode = @ goodscode
 
  
  
 
  
Set @ goodscode2 ='123' 
 
  
  
 
  
Go
 
 
 

Then we query the SYS. SQL _modules directory view and return NULL to you.

Then run the following SQL statement: exec sp_helptext 'yangyang8848. onegoods'

You will get the following results: the text for object 'angyang8848. onegoods 'is encrypted.

Here you should understand that the [encryption] parameter is an encryption function that 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.

I wrote an introduction to cursors two days ago.ArticleBelow is an example to use the cursor with the stored procedure:

 
 

  
  
 
  
If object_id ('Dbo. getmastergoods') Is not null
 
  
  
 
  
Drop proc DBO. getmastergoods
 
  
  
 
  
Go
 
  
  
 
  
 
 
  
  

  
Create proc getmastergoods
 
  
  
 
  
@ Mycursor cursor varying output
 
  
  
 
  
With Encryption
 
  
  
 
  
As
 
  
  
 
  
Set @ mycursor = cursor
 
  
  
 
  
For
 
  
  
 
  
Select goodscode, goodsname from master_goods
 
  
  
 
  
Open @ mycursor
 
  
  
 
  
Go
 
 
 

Create another stored procedure to traverse the cursor output result

 
 

  
  

  
Create proc getallgoodsidandname
 
  
  
 
  
As
 
  
  
 
  
 
 
  
  
 
  
Declare @ goodscode varchar (18)
 
  
  
 
  
Declare @ goodsname nvarchar (20)
 
  
  
 
  
Declare @ mastergoodscursor cursor
 
  
  
 
  
Exec getmastergoods @ mastergoodscursorOut 
 
  
  
 
  
Fetch next from @ mastergoodscursor
 
  
  
 
  
Into @ goodscode, @ goodsname
 
  
  
 
  
While (@ fetch_status = 0)
 
  
  
 
  
Begin
 
  
  

  
Begin
 
  
  
 
  
Print @ goodscode +':'+ @ Goodsname
 
  
  
 
  
End
 
  
  
 
  
Fetch next from @ mastergoodscursor
 
  
  
 
  
Into @ goodscode, @ goodsname
 
  
  
 
  
End
 
  
  
 
  
Close @ mastergoodscursor
 
  
  
 
  
Deallocate @ mastergoodscursor
 
  
  
 
  
Go
 
 
 

Finally, execute exec getallgoodsidandname and the result is as follows:

 
  
  


   
   
0003: Product 0003

   
   
0004: Product 0004

   
   
0005:123123

   
   
0006: Product 0006

   
   
0007: Product 0007

   
   
0008: Product 0008

   
   
0009: Product 0009

   
   
0010: Product 0010

   
   
0011: Product 0011

   
   
0012: Product 0012

   
   
0013: Product 0013

   
   
0014: Product 0014