Data room charging system-stored procedure and stored procedure

Data room charging system-stored procedure and stored procedure

I wrote a view and a trigger on my blog two days ago. I didn't want to write a stored procedure. I just wanted to use it. however, I encountered a lot of problems in the process of using them. If I don't make a conclusion, I will still use them incorrectly.

Why should I use it in the IDC charging system?

Before that, my answer is: because everyone is using it, it will definitely be used.

Now, my answer is: Because it can write and store frequently-used or complex work in SQL statements, users can directly call the stored procedure name.

 

Compared with SQL statements, stored procedures have the following advantages:

1Fast execution speed

When a stored procedure is created, it has been parsed and optimized by the database. Once executed, the stored procedure is stored in the memory, the same stored procedure can be directly called next time.

2, Reduce network traffic

If the Stored Procedure contains hundreds of rows of SQL statements, its performance will be higher than that of a single SQL statement.

3Strong adaptability

The stored procedure accesses the database of data through the stored procedure. Therefore, database developers can modify the database without modifying the stored procedure interface, these changes do not affect the program.

 

How do we use the data center charging system?

 

First, you need to know the name of the stored procedure.

At the beginning, I don't know how to name it."Proc _"Or"Sp _"What about it? Tangle cannot solve the problem, or check the internet.

All stored procedures must have the prefix "proc _", and all system stored procedures must have the prefix "sp _". Naming with the prefix "sp _" slows down the operation, because SQLServer will first look for system stored procedures.

 

I used the stored procedure in the combined query.

-- ===================================================== ====== -- Author: <Zhao yameng> -- Create date: <August 12, 2014 21:14:53> -- Description: <Combined Query> -- ================================================== =========== alter procedure [dbo]. [proc_GroupInquire] -- stored procedure name -- Add the parameters for the stored procedure here @ comName1 varchar (10), -- Define the parameter @ ComInstruction1 varchar (20), @ txtInquire1 varchar (20 ), @ comName2 varchar (10), @ ComInstruction2 varchar (20 ), @ TxtInquire2 varchar (20), @ comName3 varchar (10), @ ComInstruction3 varchar (20), @ txtInquire3 varchar (20), @ comrelationship1 varchar (20 ), @ comrelationship2 varchar (20), @ tableName varchar (20) ASdeclare @ TempSql varchar (500) -- temporarily store the SQL statement BEGINSET @ TempSql = 'select * from' + @ tableName + 'where' + @ comName1 + @ ComInstruction1 + char (39) + @ txtInquire1 + char (39) if @ comrelationship1! = ''Ininset @ TempSql = @ TempSql + @ comrelationship1 + char (32) + @ comName2 + @ ComInstruction2 + char (39) + @ txtInquire2 + char (39) if @ comrelationship2! = ''In in SET @ TempSql = @ TempSql + @ comrelationship2 + char (32) + @ comName3 + @ ComInstruction3 + char (39) + @ txtInquire3 + char (39) end endExecute (@ TempSql) END

When calling layer D, we can delete the previous SQL statements and directly call the stored procedure.

Dim strSQL As String = "proc_GroupInquire" 'call a stored procedure

My previous blog summarized the trigger. Teacher mi asked me what is the difference between the trigger and the stored procedure?

Undoubtedly, triggers are a special type of stored procedures. They are all SQL statement sets.

The difference is that the trigger is triggered by event execution, and the stored procedure is called directly by the storage name. the trigger must be attached to a specific table, and the stored procedure may not be attached. in short, the trigger is automatically executed according to the conditions, and the stored procedure needs to be manually called.

A stored procedure is an important object in the database. Any well-designed database application should consider using the stored procedure.

I am not very familiar with the use of stored procedures. I hope you can point out any errors. Thanks!

C language room Fee Management System

C language room Fee Management System
Reward score: 0-14 days and 23 hours from the end of the problem
Requirements:
(1) Input Function: Enter the student ID, class, name, start time, and end time of several students.
(2) computing function: calculate the cost of each student's computer (calculation formula: computer cost = (End Time-Start Time) * Charging Standard (in minutes ))
(3) modification function: Modify the personal files of students on the computer (for example, add or delete)
(4) query function: query the information of students on the computer by condition (class, learner, and name), and display all the corresponding files of the students.
 
Data room billing management system

Download it online, a lot