MSSQL transaction stored procedures, MSSQL transaction stored procedures

MSSQL transaction stored procedures, MSSQL transaction stored procedures

In the development of the hotel management system, we will create the room table and room type table (room type table), as shown in:

Room Type table: RoomType

Room table: Room

 

First, the relationship between the two tables: The Room is the slave table, the RoomType is the master table, and the two tables have the master foreign key relationship. The RoomType. rTypeId = Room. rTypeId

When we analyze the two tables, we will find the problem: in the room table and room table management, when we modify or delete a RoomType table, we also need to modify and delete the corresponding Room table. How can we solve this problem? First, you can set cascade deletion in the database, or use the trigger to solve, and these methods all have some drawbacks. In actual development, we will use transactions to solve this problem. This method is simple, highly efficient, and easy to maintain.

Requirement: Delete the Room table with the specified Room type while deleting the Room type.

Train of Thought: When you delete the Room type, we can use the Room type. rTypeId = Room. rTypeId to find the Room type record and delete it.

Note: deleting does not delete the entire record from the database. Changing the data ID to 1 or 0 indicates that the record is not deleted.

The following describes the stored procedure:

Code:

ALTER procedure [dbo]. [usp_DelToomType] -- create and delete a stored procedure @ rTypeId int -- room type parameter as begin transaction -- start transaction declare @ error int -- Define a variable ID error code update RoomType set rTypeIsDel = 1 where rTypeId = @ rTypeId -- delete the room type according to the condition, rTypeIsDel = 1: Id deleted; 0: id not deleted; set @ error + = @ ERROR if (@ error <> 0) -- not equal to 0 indicates that the execution of this sentence has an error rollback transaction -- transaction rollback update Room set roomIsDel = 1 where rTypeId = @ rTypeId -- delete the Room set @ error + = @@ ERROR if (@ error <> 0) -- not equal to 0 indicates that the execution of this sentence has an error rollback transaction -- transaction rollback else commit transaction -- commit transaction

Finally, the data layer directly calls the Stored Procedure Method to delete the room type and delete the room with this room type.

SQL stored procedures and transactions

Transaction format
Create procedure PRO_NAME (
P1 NVARCHAR (10)
P2 INT
)
AS
Begin transaction -- start TRANSACTION
SQL statement
IF @ ERROR <> 0 -- ERROR
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
GO
 
MSSQL Stored Procedure Problems

A stored procedure is a transaction.
If you want to update a fixed table, it is difficult to avoid locking the table with rows.

However, you can use a temporary table in your process.