< two > stored procedure (SQL) for code offloading

The use of triggers is described in the previous blog, and it also mentions that triggers are a special stored procedure, so what is a stored procedure? They

What's the difference between the two?

In fact, the main difference is that the trigger is automatically executed when the condition is met, and the stored procedure is called manually.

Simple Introduction

What is a stored procedure?

Definition: The use of common or very complex work, pre-written SQL statements and stored with a specified name, the user by specifying the name of the stored procedure

and gives the parameter (if the stored procedure has parameters) to invoke it.

In this case, someone might ask, "so is the stored procedure just a bunch of SQL statements?" So what's the difference between a stored procedure and a generic SQL statement?

It?

The stored procedure has its unique advantages, not just piling up a bunch of SQL statements into one piece:

1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, whereas a generic SQL statement is compiled every time it executes

, using stored procedures can improve database execution speed.

2. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be sealed with a stored procedure

Used in conjunction with transactional processing provided by the database.

3. Stored procedures can be reused to reduce the workload of database developers

4. High security, can be set only a certain user has the right to use the specified stored procedure

Types of stored procedures:

1. System stored procedure: Start with sp_, used to make the system settings. Get information. Related management work, such as sp_help, is to obtain the specified object of the relevant

Information

2. Extended stored procedures start with XP_ to invoke the functionality provided by the operating system

3. User-defined stored procedures, which we refer to as stored procedures

How to use

Such a good thing, how to apply it in the system?

Take the function of the machine room charge system as an example.

1. The first step is also thinking, thinking about this feature involves a few SQL statements (if you don't have to set up a stored procedure on one piece),

List the SQL statements:

delete card_info  where CID = @CID                                                                       Delete Information for the card number in the card table

delete student_info   where SID = @SID                                                                   Delete Student table correspondence number according to the number corresponding to the card number

delete line_info    where CID [email  protected]                                                                     Delete information on the corresponding card number in the on-machine record

Delete Recharge_info where CID = @CID Delete information for the corresponding card number in the recharge record

Insert into Returncard_info (Cid,returncash, UID, rdate, Rtime) VALUES (@CID, @ReturnCash, @UID, @Rdate, @Rtime) Increase the return card information to the return card record form

1. Establishing a stored procedure opens programmability under the corresponding database, creating a new stored procedure

1. Add code for the corresponding stored procedure

<span style= "Font-family:microsoft Yahei;"        >--=============================================--author: Niu Qian moved--create date:2014 June 11--Description: Return card, delete card_info,line_info,student_info,recharge_info, add returncard_info--===================================== ========alter PROCEDURE [dbo]. [Proc_returncard]--naming specification PROC_ + name--add The parameters for the stored procedure Here@cid varchar (a), @ReturnCashvarchar (), @UID varchar (20),--Define the parameters used @rdatevarchar, @Rtime varchar, @SID varcha R (Asbegin--set) NOCOUNT on added to prevent extra result sets from--interfering with SELECT statements.     Setnocount on; --Insert Statements Forprocedure here--Add the SQL statement involved deletecard_info where CID = @CIDdeleteStuden T_info where SID = @SIDdeleteLine_info where cid [email protected] deleterecharge_info WHERE cid = @CIDinserti Nto returncard_info (CID, Returncash, UID, rdate, Rtime) VALUES (@CID, @ReturNcash, @UID, @Rdate, @Rtime) end</span> 

1. Call stored procedure (layer D)

<span style= "Font-family:microsoft Yahei;" > Public Function returncard (Encardinfo asen_card_info, enstudentinfo as En_student_info, Enlineinfo as En_line_info , Enreturncardinfo as En_returncard_info) as Integer implementsireturncard.returncard Dim strSQL as String = "proc_re Turncard "' Here the strSQL no longer holds a single SQL statement, but the stored procedure ' defines the required parameters Dim Sqlparams as SqlParameter () = {Newsqlparamete                                           R ("@CID", Encardinfo.cid), Newsqlparameter ("@SID", Enstudentinfo.sid),                                           Newsqlparameter ("@ReturnCash", Enreturncardinfo.returncash), Newsqlparameter ("@UID", Enreturncardinfo.uid), Newsqlparameter ("@R Date ", enreturncardinfo.rdate), Newsqlparameter (" @Rtime ", Enreturncardinfo.rtime )} ' Note here the CommandType.StoredProcedure returnclssqlhelper.execadddelupdate (strSQL, CommanDtype.storedprocedure, Sqlparams) End function</span> 

If there is no stored procedure, complete this process, you need to write five functions: Delete card information function, delete student information function, delete

The function of the superior record, the function of deleting the top-up record, the function of adding the return card record, and then calling again, not only the code is numerous, but also the relation is disorderly;

With a stored procedure, you can simply write a function to complete the work of five functions, simplifying the code logic.

Stored procedures and triggers, in fact, is the SQL statement encapsulated in the database, the trigger can do the work, the stored procedure can generally be completed, but the choice

Use the stored procedure as a priority when choosing.

Data Reference: http://www.knowsky.com/2951.html