Transaction Processing in Stored Procedures

Create proc addfangweiandtable
(
@ Fangweiname varchar (20), --- azimuth name
@ Tablecount int, --- Number of table bits
@ Baojiancount int, --- Number of compartment
@ Returnval varchar (200) Output --- successful
)
As
Begin tran -- Development

----------------- Add orientation ------------------
If exists (select *
From fangwei
Where fangweiname = @ fangweiname)
Begin
Set @ returnval = '1'
End
Else
Begin
Insert into fangwei (fangweiname, fangweizhuo, fangweibo aojian)
Values (@ fangweiname, @ tablecount, @ baojiancount)
End

If @ error <> 0 -- Error
Begin
Set @ returnval = @ Error
Rollback tran -- rollback
Return
End

--- Variable Declaration
Declare @ tablelou char (10) --- floor
Declare @ tablenum int --- table number
Declare @ tabletype char (10) --- type
Declare @ tablebaojiannum int --- Compartment number

------------------- Add the orientation table -------------------------
Declare @ ID int
Select @ ID = 1

While @ ID <(@ tablecount + 1)
Begin
Set @ tablelou = @ fangweiname
-- Set @ tablenum = 'D' + convert (varchar, @ ID)
Set @ tablenum = @ ID
Set @ tablebaojiannum = 0
Set @ tabletype = 0

Insert into diningtable (tablelou, tablenum, tablebaojiannum, tabletype)
Values (@ tablelou, @ tablenum, @ tablebaojiannum, @ tabletype)

Set @ ID = @ ID + 1

If @ error <> 0 -- Error
Begin
Set @ returnval = @ Error
Rollback tran -- rollback
Return
End

End

 

----------------------- Add compartment --------------------------
Select @ ID = 1
While @ ID <(@ baojiancount + 1)
Begin
Set @ tablelou = @ fangweiname
Set @ tablenum = 0
-- Set @ tablebaojiannum = 'B' + convert (varchar, @ ID)
Set @ tablebaojiannum = @ ID
Set @ tabletype = 1

Insert into diningtable (tablelou, tablenum, tablebaojiannum, tabletype)
Values (@ tablelou, @ tablenum, @ tablebaojiannum, @ tabletype)

Set @ ID = @ ID + 1

If @ error <> 0 -- Error
Begin
Set @ returnval = @ Error
Rollback tran -- rollback
Return
End

End

Commit tran -- submit a transaction
Go