SQL Server is the storage process for code reduction, and SQL reduction

SQL Server is the storage process for code reduction, and SQL reduction

The stored procedure can be used to improve the database query efficiency because it has been compiled in advance and stored in the memory. It is very efficient because it does not need to be re-compiled before each execution. Stored procedures are a groupSQLAdd, delete, modify, and query sets. If a function in the program involves multiple operations on the database, you can compile the stored procedure in advance to improve the program running efficiency!

Simple Query:

CREATE PROCEDURE sp_query_online_infoAS SELECT *FROM T_OnLine_info GO

Execute the stored procedure:

EXEC sp_query_online_info 

Stored Procedures with parameters:

CREATE PROCEDURE insert_OnLine @cardno varchar(10), @ondate varchar(10),@ontime varchar(10),@AdminName varchar(10)AS INSERT INTO T_OnLine_info (cardNo,onDate,onTime,adminName)VALUES (@cardno,@ondate,@ontime,@AdminName )GO

Call this stored procedure:

Exec insert_OnLine '000000', '2017/123', '22: 22: 22', 'Liu Ying

The daily Bill settlement involves queries on multiple tables, so it is not suitable to use the stored procedure here.

Here, we will talk about the logic of the daily Bill settlement of the data center billing system. If the data center management is formal, there will be a fixed time point. The operator (who worked on the day) will come to the Administrator to settle the bill, hand over the money in hand to the Administrator, and the operator's checkout information is recorded in the database, the record contains the Recharge Amount and refund amount handled by the operator on the day, after summarizing the top-up amount and refund amount of all operators, it means the top-up amount and refund amount in the daily statement, and the consumption amount in the Daily Statement is calculated fromT_line_infoTable. The current period balance should be obtained fromT_students_infoObtained in, the balance in yesterday's card should be the day before the bill is settledSumMoneyColumn. Therefore, the daily Bill settlement refresh should also be completed by a new operator. There should be only one record in the day's statement.,Before getting off work, the Administrator must verify that the accounts in the daily statement are the same as those in his/her own account. If they are different, it means that an operator has not settled the account, and the administrator cannot get off work. It can be seen that the daily Bill settlement involves multiple tables, and the stored procedure here is not suitable.

Create procedure create_DStatementAS DECLARE @ SDate VARCHAR (10) --- checkout DATE, that is, today's DECLARE @ date as varchar (10) ---- DATE closest to today: DECLARE @ remainMoney numeric) --- yesterday's balance DECLARE @ RechargeMoney numeric (18, 0) --- today's Recharge Amount DECLARE @ consumeMoney numeric (18, 0) --- today's consumption amount DECLARE @ cancelMoney numeric (18, 0) --- refund amount today DECLARE @ sumMoney numeric () --- total amount SET @ SDate = CONVERT (VARCHAR (10), GETDATE (), 20) -- assign a value to @ SDate: delete from T_DStatement_info WHERE SDate = @ SDate ---- DELETE the record SET @ DATE = (select max (CONVERT (VARCHAR (10), SDATE) in the Daily Statement )) FROM T_DStatement_info) ---- obtain the day-End record closest to today. SELECT @ remainMoney = (SELECT sumMoney FROM T_DStatement_info WHERE SDate = @ DATE) IF @ remainMoney is null set @ remainMoney = 0 SELECT @ RechargeMoney = (select sum (CONVERT (numeric (18, 0), rechargeMoney) FROM T_Statement_info WHERE SDate = @ SDate) IF @ RechargeMoney Is nuLL SET @ RechargeMoney = 0 SELECT @ consumeMoney = (select sum (CONVERT (numeric (18, 0), consume) FROM T_Line_info WHERE offDate = @ SDate) IF @ consumeMoney is null set @ consumeMoney = 0 SELECT @ cancelMoney = (select sum (CONVERT (numeric (18, 0), cancelMoney) FROM T_Statement_info WHERE SDate = @ SDate) IF @ cancelMoney is null set @ cancelMoney = 0 SELECT @ sumMoney = (select sum (CONVERT (numeric (18, 0), cash) FROM T_Students_info) IF @ sumMoney is null set @ sumMoney = 0 insert into values (remainMoney, rechargeMoney, consumeMoney, cancelMoney, sumMoney, SDate) VALUES (@ remainMoney, @ RechargeMoney, @ consumeMoney, @ cancelMoney, @ sumMoney, @ SDate)

How can a stored procedure be stored inVB.netIn? Simple

Using the stored procedure, my daily Bill generation is much simpler than the first data center.

How can I use code to read the code content of a specified stored procedure in SQL server?

Sp_helptext stored procedure name

What is the Stored Procedure Code in SQL server 2000? If exists (select name from sysobjects

Find the stored procedure whose name is 'student _ info' and whose type is P (where P indicates procedure, stored procedure) in the entire database. If found, delete it.

If exists (select name from sysobjects where name = 'student _ info' and type = 'P ')
--- This line is used to find the stored procedure whose name is 'student _ info' and whose type is P;
Drop procedure student_info -- delete if found
Go

If no error is found, create a stored procedure named 'student _ info '.
The code above can prevent duplicate names of stored procedures and reduce errors:
The following should be:
Create procedure student_info
As select statement ......