Multiple tables of stored procedures add records

In contact with the type of MIS project, it is unavoidable to deal with the database, I believe that the operation of a single table is no Stranger (master the basic SQL statement on the line). However, with the optimization and specification of the project, the fields in the original table may have to split the attributes of the entity as the paradigm progresses, so that the functionality that a table can satisfy may now require multiple tables for simultaneous operation. Today to share with you how to use the database with the stored procedures to implement the simultaneous addition of multiple tables record function.

Stored procedure: A set of SQL statements in order to complete a specific function. Create a set of statements named Pro_studentquery (Specific pros and cons and other features can be online Baidu).

Principle: SQL statements are required for traditional single-table queries.

For example, records for Table t_working query username:

DimSQL as String="SELECT * from T_worklog [email protected]"

Calling a stored procedure is equivalent to :

Dim SQL as String= "pro_studentquery"

where pro_studentquery is the set name of the SQL statement created.

Methods: The function of multi-table operation is realized by using the storage function of the database.

Create stored procedure: "Open SQL SERVER" "Select Database" programmability "stored Procedure" right-click "New Stored Procedure"

Enter the following interface:

For example, to insert the corresponding field values into the T_student, T_card, t_recharge three tables, it is necessary to pass in parameters to the corresponding fields.

<span style= "Font-family:microsoft yahei;font-size:18px;" ><strong>create PROCEDURE Pro_studentquery@studentname Char (TEN), @StudentNo Numeric (10,3), @Sex char (10), @ Department Char, @Grade char (TEN), @Class Numeric (10,3), @CardNo Numeric (10,3), @Cash Numeric (10,3), @Explain char (10) , @Head Char, @Status char (Ten), @IsCheck char (Ten), @DateT date, @Time time, @Type char (TEN), @AddMoney Numeric (10,3) Asbegin</strong></span>

<span style= "Font-family:microsoft yahei;font-size:18px;" ><strong>set NOCOUNT on;    ----The following statements are inserted into the record, you can also write queries, delete, and so on.        INSERT INTO T_studnet (Studentname,studentno,sex, Department, Grade, Class, Cardno  ) VALUES (@StudentName, @ Studentno, @Sex, @Department, @Grade, @Class, @CardNo) </strong></span>

<span style= "Font-family:microsoft yahei;font-size:18px;" ><strong>    INSERT INTO T_card (Cardno, Cash,explain,head,status,ischeck,datet,time,type) VALUES (@CardNo , @Cash, @Explain, @Head, @Status, @IsCheck, @DateT, @Time, @Type) </strong></span>

<span style= "Font-family:microsoft yahei;font-size:18px;" ><strong>    INSERT INTO T_recharge (STUDENTNAME,CARDNO,ADDMONEY,CASH,STAUTST, Head,datet, time) VALUES (@ Studentname, @CardNo, @AddMoney, @Cash, @Status, @Head, @DateT, @Time) </strong></span>

<span style= "Font-family:microsoft yahei;font-size:18px;" ><strong>ENDGO</strong></span>

After the definition is complete, you can call it in the database by executing the following.

Project implementation: Define the entity in the U layer, assign good parameters, and finally call the stored procedure in the D layer, passing in the parameters.

Feel: The individual feels that the stored procedure is to pull out the fields that need to be manipulated from each table and combine them into a virtual table. For some functions the required values can be queried and manipulated as long as the parameters are given. Only the SQL statements are changed, and the other operations are the same as the previous single-table queries.

Multiple tables of stored procedures add records