Shulou(Shulou.com)05/31 Report--

This article focuses on "SQL Server stored procedures", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the stored procedure of SQL Server.

First of all, introduce what stored procedures are: stored procedures are commonly used or very complex work, written in advance with SQL statements and stored with a specified name, and such statements are placed in, and different SQL statements can be executed according to conditions, then in the future, when you want to call the database to provide the same functions as the defined stored procedures, you can automatically complete the command by calling execute.

Syntax:

Sql code

CREATE PROC [EDURE] procedure_name [; number]

[{@ parameter data_type}]

[VARYING] [= default] [OUTPUT]

] [,... n]

[WITH

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]

[FOR REPLICATION]

AS sql_statement [... n]

Create proc [edure] procedure_name [; number]

[{@ parameter date_type}]

[varying] [= default] [output]

] [,... n]

[with {recompile | encryption | recompile, encryption}]

[for repilcation]

As sql_statement [.. n]

Parameters:

Procedure_name

The name of the new stored procedure. The procedure name must conform to the rules for identifiers and must be unique to the database and its owner.

To create a local temporary procedure, you can add a symbol (# procedure_name) before the procedure_name, and to create a global temporary procedure, you can add two symbols (# # procedure_name) before the procedure_name. The full name (including # or #) cannot exceed 128 characters. Specifies that the name of the process owner is optional.

; number

Is an optional integer used to group procedures of the same name so that procedures of the same group can be removed with a DROP PROCEDURE statement. For example, the procedures used by an application named orders can be named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement removes the entire group. If the name contains a delimiter, the number should not be included in the identifier, only the appropriate delimiter should be used before and after the procedure_name.

@ parameter

Parameters in the process. You can declare one or more parameters in a CREATE PROCEDURE statement. The user must provide a value for each declared parameter when executing the procedure (unless a default value for that parameter is defined). A stored procedure can have up to 2100 parameters.

Use the @ symbol as the first character to specify the parameter name. The parameter name must conform to the rules for identifiers. The parameters of each procedure are used only for the process itself; the same parameter name can be used in other procedures. By default, parameters can only replace constants, not table names, column names, or other database object names.

Data_type

The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters to stored procedures. However, the cursor data type can only be used with the OUTPUT parameter. If the specified data type is cursor, you must also specify both the VARYING and OUTPUT keywords.

Note: there is no maximum number of output parameters that can be cursor data types.

VARYING

Specifies the result set that is supported as an output parameter (dynamically constructed by the stored procedure and the content can be changed). Applies only to cursor parameters.

Default

The default value of the parameter. If you define a default value, you do not have to specify a value for that parameter to execute the procedure. The default value must be constant or NULL. If the procedure will use the LIKE keyword for this parameter, the default value can include wildcards (%, _, [], and [^]).

OUTPUT

Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC [UTE]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter using the OUTPUT keyword can be a cursor placeholder.

N

Represents a placeholder that can specify up to 2100 parameters.

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE indicates that SQL Server will not cache the plan for the process, which will be recompiled at run time. Use the RECOMPILE option when using atypical or temporary values and do not want to overwrite an execution plan that is cached in memory.

ENCRYPTION represents an entry in the SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the process from being published as part of SQL Server replication.

Description: during the upgrade process, SQL Server uses the encryption comments stored in syscomments to recreate the encryption process.

FOR REPLICATION

Specifies that stored procedures created for replication cannot be executed at the subscriber. . Stored procedures created with the FOR REPLICATION option can be used as stored procedure filtering and can only be executed during replication. This option cannot be used with the WITH RECOMPILE option.

AS

Specifies the action to be performed by the procedure.

Sql_statement

Any number and type of Transact-SQL statements to be included in the procedure. But there are some restrictions.

N

Is a placeholder that indicates that this procedure can contain multiple Transact-SQL statements.

Notes

The maximum size of a stored procedure is 128 MB.

What are the advantages of stored procedures?

1. Stored procedures can only be compiled at the time of creation, and there is no need to recompile each time the stored procedures are executed, and the SQL statements we usually use are compiled every time they are executed, so using stored procedures can improve the execution speed of the database.

two。 Often encounter complex business logic and database operations, this time will use SP to encapsulate database operations. When performing complex operations on a database, such as Update,Insert,Query,Delete multiple tables, the complex operation can be encapsulated in stored procedures and used in conjunction with the transactions provided by the database. It can greatly improve the efficiency of the database and reduce the execution time of the program, which is very important in the operation of the database with a large amount of data. In terms of code, the separation of SQL statements and program code statements can improve the readability of program code.

3. Stored procedures can set parameters, and can reuse the same stored procedure according to different incoming parameters, so as to efficiently improve the optimization rate and readability of the code.

4. Highly secure, you can set the type of stored procedure that only this user has the right to use the specified stored procedure:

(1) system stored procedure: starts with sp_ and is used to set up the system. Get the information. Related management work, such as sp_help, is to obtain the relevant information about the specified object.

(2) extended stored procedures begin with XP_ and are used to call functions provided by the operating system.

Exec master..xp_cmdshell 'ping 10.8.16.1'

(3) user-defined stored procedures, which is a common format of stored procedures.

Template: Create procedure procedue_name [@ parameter data_type] [output]

[with] {recompile | encryption} as sql_statement

Explanation: output: indicates that this parameter is returnable

With {recompile | encryption} recompile: means that each time this stored procedure is executed, it is recompiled; encryption: the contents of the created stored procedure are encrypted.

Example 1: a stored procedure that returns only a single recordset.

The contents of the bank deposit table (bankMoney) are as follows

Requirement 1: stored procedures for querying the contents of table bankMoney

Sql code

Create procedure sp_query_bankMoney

As

Select * from bankMoney

Go

Exec sp_query_bankMoney

Note * in the course of use, you only need to replace the SQL statement in with the stored procedure name, which is very convenient!

Example 2 (passing parameters to the stored procedure):

Add a record to the table bankMoney and query the total amount of all deposits in userID= Zhangsan in this table.

Sql code

Create proc insert_bank

@ param1 char (10)

@ param2 varchar (20)

@ param3 varchar (20)

@ param4 int

@ param5 int output

With encryption-encryption

As

Insert bankMoney (id,userID,sex,Money)

Values (@ param1,@param2,@param3, @ param4)

Select @ param5=sum (Money) from bankMoney where userID='Zhangsan'

Go

The stored procedure is executed in the SQL Server query Analyzer by:

Sql code

Declare @ total_price int

Exec insert_bank '004Zhangsanqie' male', 100pm 'totalcake price output'.

Print 'Total balance is' + convert (varchar,@total_price)

Go

Here, let's talk about the three return values of the stored procedure (so that friends who are looking at this example do not have to check the syntax content):

1. Returns an integer as Return

two。 Return parameters in output format

3.Recordset

The difference between returning values:

Both output and return can be received as variables in the batch program, while recordset is passed back to the client running the batch

Example 3: using a simple procedure with complex SELECT statements

The following stored procedure returns all authors (provided names), published books, and publishers from a join of the four tables. The stored procedure does not take any parameters.

Sql code

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type =' P')

DROP PROCEDURE au_info_all

GO

CREATE PROCEDURE au_info_all

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors an INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

GO

Au_info_all stored procedures can be executed in the following ways:

EXECUTE au_info_all

-- Or

EXEC au_info_all

If the procedure is the first statement in a batch, you can use:

Au_info_all

Example 4: using a simple procedure with parameters

Sql code

CREATE PROCEDURE au_info

@ lastname varchar (40)

@ firstname varchar (20)

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors an INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname = @ firstname

AND au_lname = @ lastname

GO

Au_info stored procedures can be executed in the following ways:

Sql code

EXECUTE au_info 'Dull',' Ann'

-- Or

EXECUTE au_info @ lastname = 'Dull', @ firstname =' Ann'

-- Or

EXECUTE au_info @ firstname = 'Ann', @ lastname =' Dull'

-- Or

EXEC au_info 'Dull',' Ann'

-- Or

EXEC au_info @ lastname = 'Dull', @ firstname =' Ann'

-- Or

EXEC au_info @ firstname = 'Ann', @ lastname =' Dull'

If the procedure is the first statement in a batch, you can use:

Sql code

Au_info 'Dull',' Ann'

-- Or

Au_info @ lastname = 'Dull', @ firstname =' Ann'

-- Or

Au_info @ firstname = 'Ann', @ lastname =' Dull'

Example 5: a simple procedure for using wildcard parameters

Sql code

CREATE PROCEDURE au_info2

@ lastname varchar (30) ='D%'

@ firstname varchar (18) ='%'

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors an INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname LIKE @ firstname

AND au_lname LIKE @ lastname

GO

Au_info2 stored procedures can be executed in a variety of combinations. Only some combinations are listed below:

Sql code

EXECUTE au_info2

-- Or

EXECUTE au_info2 'Wh%'

-- Or

EXECUTE au_info2 @ firstname ='A%'

-- Or

EXECUTE au_info2'[CK] ars [OE] n'

-- Or

EXECUTE au_info2 'Hunter',' Sheryl'

-- Or

EXECUTE au_info2'H%','S%'

= 'proc2'

At this point, I believe you have a deeper understanding of "SQL Server stored procedures", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.