sql--Stored Procedures

Original: sql--Stored procedure

1. Why use stored Procedures

The process of using T-SQL statements to the server by an application is unsafe.

1) Data not secure

2) Each commit SQL code will be executed after syntax compilation, affecting the performance of the application running

3) Large network traffic

2. What is a stored procedure

A stored procedure is a precompiled collection of SQL statements and control statements, stored in a database, executed by an application invocation, and allows the user to declare variables, logical control statements, and other powerful programming functions. saved in SQL Server, executed by name and parameters, or returned as a result. For stored procedures I tend to think of him as a method. It can have only one query statement, or it can contain a series of SQL statements that use the control flow.

3. Advantages of stored Procedures

1) Modular Presentation design

2) Fast execution speed and high efficiency

3) Reduce network traffic

4) with good safety

4. Classification of stored procedures

1) system stored procedures

2) Extended stored procedures (one of the system stored procedures)

3) User-defined stored procedures

5. System Stored Procedures

It typically begins with "sp_" and is created, managed, and used by SQL Server, which is stored in the resource database. Similar to the method in the C # language class library, temporarily regardless of how it was written, first understand the common system stored procedures and call methods.

Common system stored procedures, see next article

Calling method: Exec[ute] Stored procedure name [parameter value]

6. Common extended Stored Procedure xp_cmdshell

xp_cmdshell It can complete some operations under DOS commands.

EXEC xp_cmdshell DOS command [no_output]

Description No_output is an optional parameter that indicates whether to output return information after the DOS command is set.

Example: Exec xp_cmdshell ' mkdir D:\newdir ' output

It is emphasized that because the user can do some operation to the operating system through xp_cmdshell, if the stored procedure is used by hackers to operate on the operating system, it is troublesome, so it usually shuts down the xp_cmdshell:

Method One:

SQL Server version 2008 and above, from the database right-click to select "Facet", in the drop-down list select "Server Security", the following list items can see the xmcmdshellenable settings.

SQL Server2005 version and below, find by starting-sqlserver-peripheral Device

Method Two:

Close xp_cmdshell

EXEC sp_configure ' show advanced options ', 1;

RECONFIGURE;

EXEC sp_configure ' xp_cmdshell ', 1;

RECONFIGURE;

Open xp_cmdshell

EXEC sp_configure ' show advanced options ', 1;

RECONFIGURE;

EXEC sp_configure ' xp_cmdshell ', 0;

RECONFIGURE;

7. User-defined stored procedures

Grammar:

Create proc[edure] Stored procedure name

@ Parameter 1 Data type = default value output,

......

@ parameter n data type = default value output

As

<sql Statement >

Go

A completed stored procedure consists of the following 3 parts:

1) input parameters, output parameters

2) T-SQL statements executed in a stored procedure

3) return value of the stored procedure

Where the input parameters allow default values.

To delete a stored procedure

drop proc Stored Procedure name

if exists (SELECT * from sysobject WHERE name = stored procedure name)

drop proc Stored Procedure name

Go

8. Precautions

Declaration of stored procedure: input parameter can have default value, output parameter can also have default value

Create proc Usp_name

@age int = 5,

@name varchar ($)         

As

......

Go

Execute statement:

exec pr_name, ' ZM '

exec default, ' ZM '

exec @name = ' ZM '

Description: For ease of invocation, it is best to put the stored procedure parameter list with default values to the last.

Stored procedure with output parameters

Create proc Usp_name

@num1 int,

@sum int Output

As

<sql Statement >

Go

Call a stored procedure

DECLARE @sum int

EXEC usp_name 5, @sum output

Note that calling a stored procedure parameter with an output parameter must be followed by the OUTPUT keyword

9. Handling errors in Stored procedures

RAISERROR ({msg_id | msg_str} {, serverity, state} [with option [,......]] ）

which

msg_id: Specify user-defined error information in the Sysmessage system table

MSG_STR: User-defined specific information, up to a maximum of 255 characters

Serverity: associated with specific information that represents a user-defined severity level. The user-selectable level is 0~18. The larger the number, the more severe the representation.

State: Indicates the status of the error, the value in the 1~255

Option: Wrong Custom option, you can make any value

LOG: Errors are logged in the error log and application log in the Microsoft SQL Server Database Engine sample

NOWAIT: Send a message to the client immediately

SetError: Set the @ @error value and the Error_number value to msg_id or 5000 regardless of the severity level.

Example: RAISERROR (' Error message ', 16,1)

sql--Stored Procedures