Database-Stored procedures.

Stored procedures:

stored Procedures (Stored Procedure) are in large database systems , a set of SQL statements to complete a particular function, compiled and stored in a database, is executed by the user 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.

Creation of stored procedures:

With the stored procedure selected, right-click-Create a new stored procedure, the following code appears.

SET ansi_nulls ongoset quoted_identifier ongo--=============================================--Author:<Author,, name>--Create Date: <create date,,>--description:<description,,>--================================ =============create PROCEDURE <procedure_name, sysname, procedurename>--ADD The parameters for the stored Procedur e here< @Param1, sysname, @p1 > <datatype_for_param1,, int> = <default_value_for_param1,, 0>, < @Para m2, sysname, @p2 > <datatype_for_param2,, int> = <default_value_for_param2,, 0>asbegin--SET NOCOUNT on ad Ded to prevent extra result sets from--interfering with SELECT statements. SET NOCOUNT on;    --Insert statements for procedure Hereselect < @Param1, sysname, @p1, < @Param2, sysname, @p2 >endgo

Built-in stored procedures:

SET ansi_nulls ongoset quoted_identifier ongo--=============================================--Author:<Author,, name>--Create Date: <create date,,>--description:<description,,>--================================ =============alter PROCEDURE [dbo]. [proc_settleaccount]--Add the parameters for the stored procedure Here@recharge numeric (18,2), @ReturnM Numeric (18,2), @i  Ncome Numeric (18,2), @UserName char (Ten), @SetDate char (Ten), @SetTime char (Ten) asbegin--SET NOCOUNT on added to prevent extra Result sets from--interfering with SELECT statements.    SET NOCOUNT on; --Insert statements for procedure Hereinsert into Bill (recharge, RETURNM, Income, UserName, SetDate, settime) VALUES (@R Echarge, @ReturnM, @Income, @UserName, @SetDate, @SetTime) Update rechargerecords Set settleaccountstate = ' Checkout ' where User Name [email protected] and settleaccountstate = ' not checkout ' update returnmoneyrecords set settleaccountstate = ' Checkout ' where U Sername [email protected] and Settleaccountstate = ' Not checkout ' update Cards set settleaccountstate = ' checked out ' where UserName [email protected] and settleaccountstate = ' not checkout ' END 

Use of stored procedures:

  Public Function Settleaccount (bill as Entity.bill) as Boolean Implements Idal. Ibill.settleaccount Dim cmdtext as String cmdtext = "Proc_settleaccount" replaces the SQL statement with the name of the stored procedure bill. SetDate = Format (now, "yyyy-mm-dd") ' Gets the current date bill. SetTime = Format (now, "HH:mm:ss") ' Get current time ' Add parameter Dim SqlParameter as SqlParameter () = {New SqlParameter ("@Rec Harge ", Bill. Recharge), New SqlParameter ("@ReturnM", Bill. RETURNM), New SqlParameter ("@Income", Bill. Income), New SqlParameter ("@UserName", Bill. UserName), New SqlParameter ("@SetDate", Bill. SetDate), New SqlParameter ("@SetTime", Bill. settime)} Dim helper As New SqlHelper Dim flag As Boolean ' parameter changed to stored procedure special parameter flag = Helper. Execadddelupdate (Cmdtext, CommandType.StoredProcedure, SqlParameter) Return flag End Function 

simply replace the SQL statement with the name of the stored procedure. At the time of execution, it is also changed to the stored procedure special parameters.

Advantages of stored procedures:

1. Re-use. Stored procedures can be reused to reduce the workload of database developers. 2. Improve performance. The stored procedure is compiled at the time of creation and will not be re-translated when it is used in the future. A typical SQL statement needs to be compiled once per execution, so using stored procedures improves efficiency. 3. Reduce network traffic. The stored procedure is located on the server, which only needs to pass the name and parameters of the stored procedure, thus reducing the amount of data transmitted over the network. 4. Security. Parameterized stored procedures can prevent SQL injection attacks, and grant, deny, and revoke permissions can be applied to stored procedures. See Baidu Encyclopedia

Summary: Using stored procedures, to a certain extent, reduce the amount of code, and try to use something that has not been used, there will be a sense of accomplishment.