SQL stored procedure definition and stored procedure benefits

SQL stored procedure definition and stored procedure benefits

SQL stored procedure definition and stored procedure benefits

SQL stored procedure definition and stored procedure benefits
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.
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.
A clr Stored Procedure refers to a reference to the. Net Framework Common Language Runtime (CLR) method. It can accept and return user-supplied parameters. They are implemented as public static methods of classes in the. Net Framework Assembly.
The statement for creating a 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]
[ [, N]
[For replication]
AS { [;] [N] | }
[;]
: =
[ENCRYPTION]
[RECOMPILE]
[EXECUTE_AS_Clause]

: =
{[BEGIN] statements [END]}

: =
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.
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 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 ('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] to this stored procedure, 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 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.
Two days ago I wrote an introduction to the cursor. 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