SQL Server Stored Procedure

I. Definition: The Stored Procedure of SQL Server is a named set of transacation-SQL statements stored on the server. It is a method for encapsulating repetitive work.

Ii. Advantages of stored procedures:

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.

Iii. Syntax: Create a stored procedure:

Syntax Create proc [edure] [ Owner .] Procedure_name[ ; Number]
[{@ Parameter data_type}
[Varying] [ = Default] [Output]
] [ , ... N ]

[
{Recompile | encryption | recompile,Encryption}]

[For replication]

AsSQL _statement[...N]

Parameters

Owner

Name of the user ID that owns the stored procedure.OwnerIt must be the name of the current user or the name of the role to which the current user belongs.

Procedure_name

Name of the new stored procedure. The process name must comply with the identifier rules and must be unique to the database and its owner.

;Number

Is an optional integer used to group processes with the same name, so that the same group of processes can be removed with a drop procedure statement. For example, an application named ordersProgramThe procedure can be namedOrderproc; 1,Orderproc; 2. Drop procedureOrderprocThe statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier.Procedure_nameUse proper delimiters before and after.

@Parameter

Parameters in the process. One or more parameters can be declared in the create procedure statement. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined or set to equal to another parameter ). A stored procedure can have a maximum of 2.100 parameters.

Use the @ symbol as the first character to specify the parameter name. The parameter name must comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects.

Data_type

The data type of the parameter. DivisionTableAll other data types can be used as parameters for stored procedures. However,CursorThe data type can only be used for output parameters. If you specifyCursorData Type, you must also specify the varying and output keywords. ForCursorThere is no limit on the maximum number of output parameters of the data type.

Varying

Specify the result set supported by the output parameter (dynamically constructed by the stored procedure, and the content can be changed ). Only applicable to cursor parameters.

Default

The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or null. If the like keyword is used for this parameter, the default value can contain wildcards (%, _, [], and [^]).

Output

Indicates that the parameter is a return parameter. The value of this option can be returned to Exec [ute]. The output parameter can be used to return information to the call process.Text,NtextAndImageParameters can be used as output parameters. The output parameter using the output keyword can be a placeholder cursor.

N

It indicates that up to 2.100 parameter placeholders can be specified.

{Recompile | encryption | recompile,Encryption}

Recompile indicates that SQL server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the recompile option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.

Encryption indicates SQL server encryptionSyscommentsThe table contains entries in the create procedure statement text. Encryption prevents the process from being published as part of SQL Server replication.

For Replication

The storage process created for replication cannot be executed on the subscription server .. Stored Procedures created using the for replication option can be used for filtering stored procedures and can only be executed during replication. This option cannot be used with the with recompile option.

As

Specifies the operation to be performed in the process.

SQL _statement

Any number and types of transact-SQL statements to be included in the process. But there are some restrictions.

N

Yes indicates that this process can contain placeholders for multiple Transact-SQL statements.

4. Usage:

**************************************** ******

Note: * The surrounding part comes from MS online books.

 

Several instances

(Content in the ajaxcity table)

Id cityname short

1SuzhouSZ

2 Wuxi City WX

3Changzhou CityCZ

1. Select all contents in the table and return a dataset.

Create procedure mysp_all
As
Select * From ajaxcity
Go

Execution result

2. query based on input parameters and return a dataset

Create procedure mysp_para
@ Cityname varchar (255 ),

@ Short varchar (255)
As
Select * From ajaxcity whereCityname = @ citynameAndShort = @ short
Go

Execution result

3. Stored Procedure with output parameters (returns the sum of the IDs of the first two Records)

Create procedure mysp_output
@ Sum int output
As
Select @ sum = sum ([ID]) from (select Top 2 * From ajaxcity) as tmptable
Go

Execution result

4. Use a cursor in the Stored Procedure

There is a table that stores information about counties and cities in various super-class cities .:

Now I want to count the number of counties and cities under each prefecture-level city and form a string. The result should be "5, 2, 2 ".

 

Create procedure mysp_cursor
@ result varchar (255) output // declare the output variable
as
declare city_cursor cursor for // declare the cursor variable
select [ID] From ajaxcity

Set @ result =''
Declare @ field int// Declare the variable that temporarily stores cityid
Open city_cursor// Open the cursor
Fetch next from city_cursor into @ Field// Assign the actual ID to the variable
While (@ fetch_status = 0)// Starts the loop
Begin
If @ result =''
Select @ result = convert (nvarchar (2), count (*) from ajaxcounty where Cityid = @ Field
Else
Select @ result = @ result + ',' + convert (nvarchar (2), count (*) from ajaxcounty where Cityid = @ Field

Fetch next from city_cursor into @ Field// The next cityid
End
Close city_cursor// Close the cursor
Deallocate city_cursor// Release cursor reference
Go

 

Execution result

 

Well, write the stored procedure here first. the above examples basically implement most of the functions commonly used. as for complex stored procedures, the SQL syntax is used, and the use of built-in functions in SQL. it is no longer within the scope of this article.