SQL Server-Stored Procedure

Two days ago, we briefly learned about a special stored procedure-trigger. Today we want to write some simple theoretical and practical knowledge about the stored procedure.

First, we need to understand what is a stored procedure and its format.

Definition:Store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.

Format:

-- ===================================================== ====== -- Author: <author> -- Create date: <creation date> -- Description: <description> -- ============================================ ========== create procedure <Stored PROCEDURE Name> -- add a stored PROCEDURE parameter <@ parameter, data Type •> ASBEGIN -- program line, specific operation process ENDGO

PS: the stored procedure name can contain a maximum of 128 characters, and a maximum of 1024 parameters can be added (which can be left blank). It can be executed by the exec stored procedure name.

Here is an example.Practical ApplicationStored Procedure

USE [DB_rechargeSystem] GO/****** Object: StoredProcedure [dbo]. [proc_T_UserInfoDelete] Script Date: 11:00:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: old ox -- Create date: -- Description: delete user information -- =============================================== ========= create procedure [dbo]. [proc_T_UserInfoDelete] @ userID int ASBEGIN -- first delete user information delete T_UserInfo where userID = @ userID END

PS: delete user information through userID.

1. Check whether the execution is successful. Let's take a look at the information in the T_UserInfo table first.

2. Execute the Stored Procedure

exec proc_T_UserInfoDelete 3

3. view results

PS: we can find that the stored procedure is successfully executed.

The above is a brief introduction to stored procedures. Why should we use stored procedures? When should we use stored procedures? In fact, we can use the memory when we encounter another problem.

A. in data, an operation appears multiple times (reuse)

B. High system performance requirements (improved performance)

C. When processing highly concurrent data operations (reducing network traffic)

D. The requirement for system security is (to improve security)

 

(If there are any omissions in cainiao's work, please do not hesitate to inform me !)