< two > stored procedure (SQL) for code offloading

The use of triggers is described in the previous blog, and the trigger is a special stored procedure, so what is a stored procedure? They

What is the difference between the two?

In fact, the most basic difference is that the trigger is run by the system itself when the condition is met, and the stored procedure is called manually.

Introduction

What is a stored procedure?

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

and gives a reference (assuming that the stored procedure has a parameter) to invoke it.

Speaking of which, there may be people asking: so is the stored procedure just a bunch of SQL statements? So what's the difference between a stored procedure and a typical SQL statement?

It?

Stored procedures have a unique advantage not just to pile up a bunch of SQL statements into one piece:

1. The stored procedure is compiled only at creation time, and each subsequent run of the stored procedure does not need to be compiled again, and the general SQL statement is compiled every time it is run

, you can use stored procedures to increase the speed of your database operations.

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 the Database developer

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

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 (assuming there is no need to set up a stored procedure),

List the SQL statements:

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

Delete Student_info where SID = @SID Delete the student table corresponding number information according to the corresponding number of the card number

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

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

1. Add code to the corresponding stored procedure

--=============================================--author: Niu Qian moved--create date:2014 June 11--descripti On: Return card, delete card_info,line_info,student_info,recharge_info, join returncard_info--================================ at the same time =============alter PROCEDURE [dbo]. [Proc_returncard]--naming specification PROC_ + name--add The parameters for the stored procedure Here@cid varchar (a), @ReturnCashvarchar (), @UID varchar (20),--Join @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--join the SQL statement involved deletecard_info where CID = @CIDdeleteStuden T_info where SID = @SIDinsertinto 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 as En_card_info, enstudentinfo as En_student_info, Enlineinfo as En_line_inf O,enreturncardinfo as En_returncard_info) as Integer implementsireturncard.returncard Dim strSQL as String = "Proc_r Eturncard "' strSQL here no longer holds a single SQL statement, but rather a stored procedure ' defines the required number of parameters Dim Sqlparams as SqlParameter () = {Newsqlparamet                                           ER ("@CID", Encardinfo.cid), Newsqlparameter ("@SID", Enstudentinfo.sid),                                           Newsqlparameter ("@ReturnCash", Enreturncardinfo.returncash), Newsqlparameter ("@UID", Enreturncardinfo.uid), Newsqlparameter ("@ Rdate ", Enreturncardinfo.rdate), Newsqlparameter (" @Rtime ", Enreturncardinfo.rtim e)} ' Note that the CommandType here is no longer a text command (CommandType.Text) but CommandType.StoredProcedure returnclssqLhelper.execadddelupdate (strSQL, CommandType.StoredProcedure, sqlparams) End function</span> 

Assuming there is no stored procedure, the process needs to write five functions: Delete card information functions, delete student information functions, 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 chaotic;

Using a stored procedure simply requires writing a function to complete the work of five functions, simplifying the logic of the code.

Stored procedures and triggers, in fact, the SQL statements are encapsulated in the database, the trigger can complete 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

< two > stored procedure (SQL) for code offloading