"Machine room Refactoring" SQL stored procedure

The previous blog describes the use of SQL views, a blog that provides a brief introduction to stored procedures through content and instance applications. In the process of machine room reconstruction, made a big taboo (database design in the reconstruction process was modified), so the impact of a function of the implementation, and then re-knocked the computer room charge system back card function. Just as "disguise", the pure three-layer code implementation becomes the perfect implementation after the use of stored procedures. The stumbling of the period has allowed us to deepen our understanding of the stored procedure.

Stored procedures:Definition:

When you need to write multiple SQL statements, you can use stored procedures to establish stored procedures in the database. This is then used by calling the stored procedure in vs.

Baidu Encyclopedia: Stored Procedures (Stored Procedure) are in a large database system , a set of SQL statements to complete a specific function, compiled and stored in the database, The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.

Advantages:

(from Baidu) The more you use, the more you can appreciate its benefits, now understand is not very deep, can only learn from the network.

        1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can increase the speed of the database execution.　　2. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the 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 access to the specified stored procedure

use:

1. Before using, the stored procedure is now established in SQL Sever through the T_SQL statement. As shown, right-click Stored procedures and select New stored procedure.

Then enter the code to create the stored procedure in the window that appears, as follows:

CREATE procedure [dbo]. [Proc_cancelcard]  --Write the name of the stored procedure pro_cancelcard--the following are the defined parameters, that is, when you use this procedure, you need to pass in the parameters of the values. @CardNo varchar (@CancelDate date, @CancelTime varchar, @Statue varchar, @CancelUserID varchar) AS-- The declared variable declare    @CancelCash numeric (2), @StudentNo varchar (one-by-one  ) begin--assign a query directly to the defined variable Select<span style= " Color: #ff0000; " > @CancelCash =cash, @StudentNo =studentno</span> from V_studentinfo  where Cardno [email protected]  -- Insert the incoming and assigned variables into the return table insert into T_cancelcardinfo (Studentno,cardno,cancelcash,canceldate,canceltime,statue, Canceluserid) VALUES (@StudentNo, @CardNo, @CancelCash, @CancelDate, @CancelTime, @Statue, @CancelUserID)-- Updated the status in the card table Statueupdate t_cardinfo Set statue = ' deactivated ' End

NOTE: If the SQL statement in your stored procedure is the first query, and the second one uses the query results, you do not have to define the parameters to directly define the variables for the assignment query as shown in the red Word .

2. Calling a stored procedure in VS

D-Tier Code:

    ' <summary> ' Call the stored procedure to insert, update ' </summary> ' ' <param name= ' encancel ' ></param> "' <param name=" Eninfo "></param>" "<param name=" Enline "></param>" <returns>boole An type variable </returns> ' ' <remarks></remarks> public Function cancelcarddal (ByVal encancel as Entity.can  Celcardentity, ByVal Eninfo as entity.v_studententity, ByVal Enline as entity.lineinfoentity) as Boolean Dim Helper        As New SqlHelper ' Create SqlHelper Object Dim flag As Boolean Dim sql As String = "Proc_cancelcard" The name of the stored procedure created Dim params as SqlParameter () = {New SqlParameter ("@CardNo", Encancel.cardno), N  EW SqlParameter ("@CancelUserID", Encancel.canceluserid), New SqlParameter ("@Statue",                                    Encancel.statue), New SqlParameter ("@CancelDate", Encancel.canceldate),   New SqlParameter ("@CancelTime", encancel.canceltime)} flag = Helper. ExecuteNonQuery (SQL, CommandType.StoredProcedure, params) return flag ' returns a Boolean End Function

return Card function description :

1. In the user input card number, should first go to inquire whether there is this card number, whether it is on the computer;

2. You should then check the balance, study number (this query uses the view, but later after the database discovery, this view can not be used, when it is practiced hand practice) and the current time, the user and so on to the return card table add a record;

3. Update the status of the card in the card table afterwards;

4. The return card information is displayed, indicating that the card is successful.

Because before the pure three layer of code, and this version of the comparison, directly shows the difference. Code many, call the relationship disorder, very test the human brain. Stored procedure one top multiple SQL, why not?