Detailed SQL Server stored procedures

SQL Server's stored procedure is a named collection of transacation-sql statements stored on the server, a way to encapsulate repetitive work, which supports user-declared variables, conditional execution, and other powerful programming capabilities.

SQL Server's stored procedure is a named collection of transacation-sql statements stored on the server, a way to encapsulate repetitive work, which supports user-declared variables, conditional execution, and other powerful programming capabilities.

Stored procedures have the following advantages over other database access methods:

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

(2) Improve performance. Stored procedures are compiled when they are created and are not recompiled for future use. Normal SQL statements need to be compiled once per execution, so using stored procedures improves efficiency.

(3) Reduce network traffic. The stored procedure is on the server, and when invoked, it is only necessary to pass the name of the stored procedure and the parameters, thus reducing the amount of data transmitted by the network.

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

Stored procedures are divided into three categories: User-defined stored procedures, extended stored procedures, and system stored procedures.

Among them, user-defined stored procedures are divided into Transaction-sql and CLR two types.

A transaction-sql stored procedure is a collection of saved Transaction-sql statements that can accept and return user-supplied parameters.

A CLR stored procedure is a reference to the. Net Framework common Language Runtime (CLR) method that can accept and return user-supplied parameters. They are implemented as a public static method of a class in the. Net Framework assembly. (This article will not be introduced)

The statement that creates the stored procedure is as follows:

The following are the referenced contents:

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]: Represents the name of the schema to which the stored procedure belongs

For example:

Create Schema yangyang8848
Go
Create Proc yangyang8848. Allgoods
As Select * from Master_goods
Go

Execution: An error occurred in Exec Allgoods.

Executive: Exec yangyang8848. Allgoods properly executed.

[; number]: An optional integer that is used to Group a procedure of the same name. Use a drop PROCEDURE statement to delete these grouping procedures together.

For example:

            Create Proc S1 1
             as
                    Select * from Master_goods
             Go
             Create Proc S1 2
            as
  & nbsp;                Select * FROM Master_location
            Go
  created two stored procedures. They are in the same group S1, and if exec S1 is executed, the stored procedure defaults to exec S1; 1. EXEC S1 2 is required if we want all of our location information. When we want to delete a stored procedure, only the drop Exec S1 is executed and all stored procedures in that group are deleted.

[@ parameter]: A parameter in a stored procedure that the user must assign a value to when the stored procedure is invoked, unless the parameter is defined with a default value or if the parameter is set to equal another parameter.

Stored procedures have a maximum of 2,100 parameters.

For example:

Create Proc yangyang8848. Onegoods
@GoodsCode varchar (10)
As
Select * from master_goods Where goodscode = @GoodsCode
Go

Code to invoke:

Declare @Code varchar (10)
Set @Code = ' 0004 '
Exec yangyang8848. Onegoods @Code

Adding output behind the parameter indicates that the parameter is an output parameter.

Create Proc yangyang8848. Onegoods
@GoodsCode2 varchar () output, @GoodsCode varchar (10) = ' 0011 '
As
Select * from master_goods Where goodscode = @GoodsCode
Set @GoodsCode2 = ' 0005 '
Go

Call Method:
Declare @VV2 varchar (10)
Exec yangyang8848. Onegoods @Code out

Note: If the stored procedure has a default value of two parameters one does not, then we have to put the default is worth putting behind, otherwise there will be problems oh ~ ~

Careful friend, may see the above statement some different, for example, stored procedures with output, and call statements with out. I want to tell you that the two are the same.

[RECOMPILE]: A plan that instructs the database engine not to cache the process, which is compiled at run time. This option is not available if a for REPLICATION is specified. For CLR stored procedures, RECOMPILE cannot be specified.

It says a very useful function object_id: Returns the database object identification number of the schema-scoped object.

For example, when we create a stored procedure, we can write code as follows

If object_id (' yangyang8848. Onegoods ') is not Null
Drop Proc yangyang8848. Onegoods
Go

Create Proc yangyang8848. Onegoods
@GoodsCode2 varchar (+) out, @GoodsCode varchar (10) = ' 0011 '
As
Select * from master_goods Where goodscode = @GoodsCode
Set @GoodsCode2 = ' 0005 '
Go

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

Select definition from sys.sql_modules
Where object_id = object_id (' yangyang8848. Onegoods ');

We can find out the result.
But if we add [encryption] to the stored procedure, then you won't see any results.

If object_id (' yangyang8848. Onegoods ') is not Null
Drop Proc yangyang8848. Onegoods
Go

Create Proc yangyang8848. Onegoods
@GoodsCode2 varchar (+) out, @GoodsCode varchar (10) = ' 0011 '

With encryption
As
Select * from master_goods Where goodscode = @GoodsCode
Set @GoodsCode2 = ' 0005 '
Go

Then we query the Sys.sql_modules catalog view and will return you null.

Then we perform the following sql:exec sp_helptext ' yangyang8848. Onegoods '

You will get the following result: The text for object ' yangyang8848. Onegoods ' is encrypted.

Speaking of which, you should understand. parameter [encryption]: is an encryption feature that converts the original text of a CREATE PROCEDURE statement into a fuzzy format. The output of the fuzzy code cannot be displayed directly in any catalog view of SQL Server 2005. Users who do not have access to system tables or database files cannot retrieve fuzzy text. However, this text can be used by privileged users who access the system tables through the DAC port or by privileged users who have direct access to the database files. In addition, a user who is able to attach a debugger to a server process can retrieve the decrypted process from memory at run time.

Two days ago I wrote an introductory article about cursors, and here's an example of using cursors with stored procedures:

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 below to traverse the cursor output

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

The final executive Exec Getallgoodsidandname results are as follows

0003: Product 0003
0004: Product 0004
0005:123,123
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