An example of SQL Server game tagging skills

When a problem occurs, it is accidental. Maybe you have never been concerned about it, but it does not mean that you will not encounter the same (similar) in the future). There are many examples related to the SQL midstream standards. Here I will combine the actual examples to explain the SQL application skills, in this article, we mainly use the game tag to add multiple operators.

Question:

The following three tables are related to the scheduling schedule:

<! --

Course schedule: t_kcb

Xuen, xueq, banj, xingq, jiec, kech

Lesson schedule: t_tk

Xuen, xueq, banj, Zhouc, xingq, jiec, yuankch, shijkch, tiaokyy

Real-time sequence table: t_sskb

Xuen, xueq, banj, Zhouc, xingq, jiec, kech, beizh

-->


The course schedule is well maintained at the beginning of school and does not need to be changed. The course schedule records course changes in the middle. Real-time course tables are automatically generated based on the preceding two tables.


You need to perform the following operations to set the parameter process:

After selecting "xuen", "xueq", "banj", and "Zhouc", read the data from the temporary Course table, insert the curriculum data into the real-time curriculum, and add the last week (Zhouc). If it is not empty, the corresponding week (xingq), section (jiec) the actual course after the temporary adjustment (shijkch), and then add the reason for the transfer (tiaokyy) in the remarks (beizh)

Xuen, xueq, banj, and Zhouc.

The solution to the problem is as follows. There is a comment in this article, which is not described here.
Create   Procedure Foo

@ Xuen Int , -- External ingress

@ Xueq Int ,

@ Banj Int ,

@ Zhouc Int

As

Declare @ Jiec Int , -- Internal changes

@ Xingq Int ,

@ Kech Char ( 200 )

Begin

-- First, you need to search for information from the databases table and perform update operations on the Real-time tables.

Update T_sskb Set Kech = TK. shijkch, beizh = TK. tiaokyy From T_sskb, t_tk TK Where  

TK. xuen = @ Xuen And TK. xueq = @ Xueq And TK. banj = @ Banj And TK. Zhouc = @ Zhouc

And KB. xuen = TK. xuen And KB. xueq = TK. xueq And KB. banj = TK. banj And KB. Zhouc = TK. Zhouc

And KB. xingq = T_sskb.xingq And KB. jiec = T_sskb.jiec


-- If the return result is 0, it indicates that no related records exist in the entire table. At this time, the related logs are searched from the entire table for the insert operation.

If @ Rowcount = 0  

Begin

Declare My_cursor Cursor   For -- Define a game Standard for Tracking and Processing



Select Xingq, jiec, kech From T_kcb Where  

Xuen = @ Xuen And Xueq = @ Xueq And Banj = @ Banj And Zhouc = @ Zhouc



Open My_cursor -- Set the game opening Standard



Fetch   Next   From My_cursor -- The results of the query are stored in the variable for processing.

Into @ Xingq, @ jiec, @ kech



While @ Fetch_status =   0 -- Fetch refers to the processing of malicious transactions.

Begin

-- Ignore row insert operation

Insert   Into T_sskb (xuen, xueq, banj, Zhouc, xingq, jiec, kech) Values

(@ Xuen, @ xueq, @ banj, @ Zhouc, @ xingq, @ jiec, @ kech)

If @ Error <> 0

Begin

Raiserror ( ' Error ' , 16 , 1 ) -- Routine handling

Rollback   Transaction

End

Fetch   Next   From My_cursor -- Handle the next record

Into @ Xingq, @ jiec, @ kech

End

Close My_cursor -- Mark

Deallocate My_cursor -- Benchmark

End

End

In the above example, the insert process can be replaced by the insert into statement, which is more concise and efficient. The reason why I use a cursor is to give an example of a cursor application, since someone asks a question, I will write out another solution. (2005.1.31)
Create   Procedure Foo

@ Xuen Int , -- External ingress

@ Xueq Int ,

@ Banj Int ,

@ Zhouc Int  

As  

Declare @ Jiec Int , -- Internal changes

@ Xingq Int ,

@ Kech Char ( 200 )

Begin  

-- First, you need to search for information from the databases table and perform update operations on the Real-time tables.

Update T_sskb Set Kech = TK. shijkch, beizh = TK. tiaokyy From T_sskb, t_tk TK Where

TK. xuen = @ Xuen And TK. xueq = @ Xueq And TK. banj = @ Banj And TK. Zhouc = @ Zhouc

And KB. xuen = TK. xuen And KB. xueq = TK. xueq And KB. banj = TK. banj And KB. Zhouc = TK. Zhouc

And KB. xingq = T_sskb.xingq And KB. jiec = T_sskb.jiec

 

-- If the return result is 0, it indicates that no related records exist in the entire table. At this time, the related logs are searched from the entire table for the insert operation.

If @ Rowcount = 0

Begin  


-- // The insert into method is directly used here.

Insert   Into T_sskb Select Xuen, xueq, banj, @ Zhouc, xingq, jiec, kech, Space ( 1 ) From T_kcb

Where Xuen = @ Xuen And Xueq = @ Xueq And Banj = @ Banj

-- //



If @ Error = 0

Begin

Raiserror ( ' Error ' , 16 , 1 )

Rollback   Transaction

End

End

End

Note: when using the "insert into Table1 select * From Table2" sentence for insert query, if the fields defined by the two tables are different, the displayed field must be used in the SELECT statement to satisfy the field definition in Table 1, instead of.

Please note: cnblogs (Bonny. Wong) 2005.1.27