SQL Server Stored Procedure overview and usage

SQL
The 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 user-declared variables,
Conditional execution and other powerful programming functions.

Compared with other database access methods, stored procedures have the following advantages:

(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.
Therefore, using Stored Procedures improves the efficiency.

(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 it. Therefore, the network transmission is reduced.
Data volume.

(4) Security. Parameterized stored procedures can prevent SQL injection attacks, and Grant, deny, and revoke permissions can be applied
Stored procedure.

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.

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

The CLR Stored Procedure refers to
The reference of the Framework Common Language Runtime (CLR) method can accept and return user-supplied parameters. They are in. Net
The Framework Assembly is implemented as a public static method of the class.(
This article will not be introduced)

The statement for creating a stored procedure is as follows:

Document. getelementbyid ('Code _ closed_text_170611 '). style. Display = 'none ';

Document. getElementById ('Code _ Open_Image_170611 '). style. display = 'inline ';

Document. getElementById ('Code _ Open_Text_170611 '). style. display = 'inline'; "src =" http://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif "alt =" "width =" 11 "height =" 16 "align =" top "> document. getElementById ('Code _ Open_Text_170611 '). style. display = 'none ';
GetElementById ('Code _ Closed_Image_170611 '). style. display = 'inline ';
GetElementById ('Code _ Closed_Text_170611 '). style. display = 'inline'; "src =" http://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif "alt =" "width =" 11 "height =" 16 "align =" top "> Code

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]:

The 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]:
Used
An optional integer that groups 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.
All stored procedures in the group are deleted.

[@ Parameter]:
Stored procedure parameters, unless the parameter is defined
If there is a default value or the parameter is set to equal to another parameter, otherwise you must assign a value to the parameter when calling the stored procedure.

The stored procedure can contain up to 2100 parameters.

For example:

Create proc yangyang8848.onegoods
@ Goodscode
Varchar (10)
As
Select * From master_goods
Where goodscode = @ goodscode
Go

Call code:

Declare @ code varchar (10)
Set @ code = '000000'
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) = '20140901'
As
Select
* From Master_Goods Where GoodsCode = @ GoodsCode
Set
@ GoodsCode2 = '20140901'
Go

Call method:
Declare @ VV2 varchar (10)
Exec
Yangyang8848.OneGoods @ Code out

Note: if either of the two parameters of the stored procedure has a default value or none
We need to put the default value behind it, or something will happen ~~

Careful friends may see some differences in the above statements, such as storage
The process uses output, while the call statement uses out. I want to tell you that the two are the same.

 

[Recompile]:
Indicates database reference
Worker does not cache the plan of the process, which is compiled at runtime. If for replication is specified, this option cannot be used. The CLR stored procedure cannot be specified.
Recompile.

 

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 ('yangyang8848. onegoods ') is not null
Drop
Proc yangyang8848.onegoods
Go

Create Proc yangyang8848.OneGoods
@ GoodsCode2
Varchar (10) out, @ GoodsCode varchar (10) = '20140901'
As
Select
* From Master_Goods Where GoodsCode = @ GoodsCode
Set
@ GoodsCode2 = '20140901'
Go

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

Select definition From sys. SQL _modules
Where
Object_id = Object_ID ('angyang8848. OneGoods ');

We can check the results.

However, if we add [
ENCRYPTION
]
You will not be able to see any results

If Object_ID ('yangyang8848. OneGoods ') Is Not Null
Drop
Proc yangyang8848.OneGoods
Go

Create Proc yangyang8848.OneGoods
@ GoodsCode2
Varchar (10) out, @ GoodsCode varchar (10) = '20140901'

With Encryption
As
Select * From
Master_Goods Where GoodsCode = @ GoodsCode
Set
@ GoodsCode2 = '20140901'
Go

Then we querysys.sql_modules
Directory view, which returns 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 the parameter [
ENCRYPTION
]
: Is an encryption function, the CREATE PROCEDURE
The original text of the statement is converted to the fuzzy format. The output of fuzzy code is in SQL Server 2005
Cannot be directly displayed in any directory view. Users who do not have access to system tables or database files cannot retrieve fuzzy text. However, you can use the DAC
This text can be used by privileged users who access system tables or those who access database files directly. In addition, users who can append a debugger to server processes can retrieve decrypted processes from the memory at runtime.

 

I wrote an article about games two days ago.
Subject description
Below 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 @ mastergoodscursor out
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