SQL Server stored procedures for code offloading

A stored procedure can be used to improve the efficiency of a database query because it is compiled beforehand, stored in memory, and does not have to be newly compiled before each execution, so it is highly efficient. A stored procedure is a set of SQL Additions and deletions, and if a function in the program involves multiple operations on the database, you can compile the stored procedures beforehand to improve the efficiency of the program!

Simple Query :

CREATE PROCEDURE sp_query_online_infoas SELECT *from t_online_info GO

To execute a stored procedure:

stored procedure with parameters:

CREATE PROCEDURE insert_online @cardno varchar, @ondate varchar (TEN), @ontime varchar (@AdminName varchar) as INSERT into T_online_info (cardno,ondate,ontime,adminname) VALUES (@cardno, @ondate, @ontime, @AdminName) GO

Call this stored procedure:

exec insert_online ' 00001 ', ' 8/21/2014 ', ' 22:22:22 ', ' Liu Ying

The daily checkout list involves multiple table queries, so it's a good place to use stored procedures.

in this case, the computer room charge system of the day billing logic, if the computer room management is regular, there will be a fixed point in time, the operator (the same day worked) to find the administrator checkout, will hand the money to the administrator, and the operator billing information recorded in the database, The record contains the amount of recharge and refund amount that the operator handled on the same day, and the sum of the amount and the amount of the refund paid by all operators, which is the amount and amount of the refund in the daily checkout order, and the amountT_line_infotable, the balance of this issue should be obtained fromT_students_infoThe balance of yesterday card should be the day before the billSummoneycolumn. So the daily checkout order refresh is also to be in the case of a new operator knot posting. One day should only be recorded in the Daily checkout order,every day before work, the administrator must verify that the accounts in the daily bill and the accounts in their own hands in order to work, if not consistent, it means that the operator has not yet checked out, the administrator can not work. Visible Day Checkout is involved in more than one table, here with the stored procedure in the appropriate

CREATE PROCEDURE create_dstatementas DECLARE @SDate varchar---Checkout date, which is today DECLARE @DATE as VARCHAR---- Today's recent date declare @remainMoney numeric (18,0)---Yesterday's balance declare @RechargeMoney numeric (18,0)---recharge today declare @consumeMoney  Numeric (18,0)---Today's consumption amount declare @cancelMoney numeric (18,0)---Today's withdrawal amount declare @sumMoney numeric (18,0)---Total amount set @SDate =convert (VARCHAR), GETDATE (), 20)--Assigning a value to @sdate delete from T_dstatement_info WHERE [email protected]---- Delete Day Checkout consignments today's record set @DATE = (SELECT MAX (sdate) from t_dstatement_info)----Get the nearest day of today's Day knot record SELECT @ Remainmoney = (SELECT Summoney from T_dstatement_info WHERE [email protected]) IF @remainMoney is NULL SET @remainMo Ney =0select @RechargeMoney = (SELECT SUM (CONVERT (numeric (0), Rechargemoney)) from T_statement_info WHERE [email  protected]) IF @RechargeMoney is a nuLL SET @RechargeMoney =0select @consumeMoney = (SELECT SUM (CONVERT (numeric (18, 0 ), consume)) from T_line_info WHERE offdate [email protected]) IF @cOnsumemoney is NULL SET @consumeMoney =0select @cancelMoney = (The SELECT SUM (CONVERT (numeric (0), Cancelmoney)) from T_ Statement_info WHERE sdate [email protected]) IF @cancelMoney is a NULL SET @cancelMoney =0select @sumMoney = (SELECT SU M (CONVERT (numeric (0), cash)) from T_students_info) IF @sumMoney is a NULL SET @sumMoney =0insert into T_dstatement_info ( Remainmoney,rechargemoney,consumemoney,cancelmoney, Summoney,sdate) VALUES (@remainMoney, @RechargeMoney, @ Consumemoney, @cancelMoney, @sumMoney, @SDate)

So how does the stored procedure vb.net call it in? It's simple .

The use of stored procedures, so that my daily bill to generate more than the first time the computer room, relatively simple a lot.

SQL Server stored procedures for code offloading