SQL Server stored procedures for code offloading

Stored procedures can be used to improve the query efficiency of the database. Because it was compiled beforehand. is stored in memory, before each execution. It doesn't have to be newly compiled, so it's very efficient.

A stored procedure is a set of SQL The collection of additions and deletions, assuming that a function in the program involves multiple operations on the database. Then you can compile the stored procedure beforehand. To improve program execution efficiency!

Simple Query :

CREATE PROCEDURE sp_query_online_infoas SELECT *from t_online_info GO

To run a stored procedure:

stored procedure with a number of 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 tables of queries, so it is only appropriate to use stored procedures here

Here is the logic of the Daily checkout order of the computer room charge system. If the computer room management is normal, 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 checkout information recorded in the database, the record includes the day of the operator's recharge amount and refund amount, After summing up the amount and the amount of the refund for all operators, it is the amount and amount of the refund in today's daily checkout order. The amount of consumption in the daily checkout order is fromT_line_infoThe remaining amount in the current period should be obtained from theT_students_infoThe remaining amount of yesterday's 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,The administrator must verify that the accounts in the daily Bill are in line with the accounts in their own hands before leaving work every day. Assuming inconsistencies, it means that an operator has not yet checked out and the administrator cannot leave work.

Visible Day Checkout is involved in more than one table, here with the stored procedure in the appropriate just

CREATE PROCEDURE create_dstatementas DECLARE @SDate varchar---Checkout date, which is today DECLARE @DATE as VARCHAR---- From today's recent date declare @remainMoney numeric (18,0)---Yesterday's remaining amount declare @RechargeMoney numeric (18,0)---Today recharge amount DECLARE @ Consumemoney numeric (18,0)---Today's consumption declare @cancelMoney numeric (18,0)---Today's withdrawal amount declare @sumMoney numeric (18,0)--- Total amount Set @SDate =convert (VARCHAR), GETDATE (), 20)--assigning @sdate to delete from T_dstatement_info WHERE [email protected ]----Delete Day checkout consignments today's record set @DATE = (SELECT MAX (CONVERT (VARCHAR), sdate) from T_dstatement_info)---- Get distance from today's recent day-to-day knot record Select @remainMoney = (select Summoney from T_dstatement_info WHERE [email protected]) IF @ Remainmoney is NULL SET @remainMoney =0select @RechargeMoney = (The SELECT SUM (CONVERT (numeric (0), Rechargemoney)) from t_s  Tatement_info WHERE [email protected]) IF @RechargeMoney is a nuLL SET @RechargeMoney =0select @consumeMoney = (SELECT SUM (CONVERT (numeric (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? Very easy

The use of stored procedures, so that my daily checkout order generation, than the first time room. It's relatively simple and very much.

SQL Server stored procedures for code offloading