Create proc RegisterUser (@usrName varchar(30), @usrPasswd varchar(30),@age int, @PhoneNum varchar(20), @Address varchar(50) ) as begin //显示定义并开始一个事务 begin tran insert into USER(userName,userPasswd) values(@usrName,@usrPasswd) if @@error<>0 begin //操作失败,则事务回滚 rollback tran //返回存储过程,并设置返回码为事务操作失败 return -1 end insert into USERDOC(userName,age,sex,PhoneNumber,Address) values(@Usrname,@age,@PhoneNum,@Address) if @@error<>0 begin //操作失败,则事务回滚 rollback tran return -1 end //如果操作执行正确,则提交事务 commit tran return 0 end
游标实例 USE [Unigo.Com] GO DECLARE @UserId int SET @UserId=1144 DECLARE @UserId_tmp int DECLARE @MsgId_tmp int DECLARE DELETE_MESSAGE CURSOR FOR SELECT user_to_p_msg.user_id ,p_msg.private_message_id FROM [dbo].[p_msg] INNER JOIN [dbo].user_to_p_msg ON [dbo].[p_msg].private_message_id = [dbo].user_to_p_msg.private_message_id where [dbo].[p_msg].user_id =@UserId OPEN DELETE_MESSAGE FETCH NEXT FROM DELETE_MESSAGE INTO @UserId_tmp,@MsgId_tmp WHILE @@FETCH_STATUS = 0 BEGIN Print '---'+ cast(@UserId_tmp as varchar)+'---'+cast(@MsgId_tmp as varchar) EXEC [dbo].[Unigo_Community_DeletePrivateMessage] @UserId_tmp,@MsgId_tmp FETCH NEXT FROM DELETE_MESSAGE INTO @UserId_tmp,@MsgId_tmp END CLOSE DELETE_MESSAGE DEALLOCATE DELETE_MESSAGE -----DELETE Two DECLARE DELETE_MESSAGE_2 CURSOR FOR SELECT user_to_p_msg.user_id,p_msg.private_message_id FROM [dbo].[p_msg] LEFT OUTER JOIN [dbo].user_to_p_msg ON [dbo].[p_msg].private_message_id = [dbo].user_to_p_msg.private_message_id where [dbo].user_to_p_msg.user_id =@UserId OPEN DELETE_MESSAGE_2 FETCH NEXT FROM DELETE_MESSAGE_2 INTO @UserId_tmp,@MsgId_tmp WHILE @@FETCH_STATUS = 0 BEGIN Print '---'+ cast(@UserId_tmp as varchar)+'---'+cast(@MsgId_tmp as varchar) EXEC [dbo].[Unigo_Community_DeletePrivateMessage] @UserId_tmp,@MsgId_tmp FETCH NEXT FROM DELETE_MESSAGE_2 INTO @UserId_tmp,@MsgId_tmp END CLOSE DELETE_MESSAGE_2 DEALLOCATE DELETE_MESSAGE_2 IF @@ERROR=0 BEGIN --Delete P_MSG Info delete P_MSG where user_id=@UserId --Delete UserFriendBlackList END GO USE [Unigo] GO DECLARE @UserId int SET @UserId=1144 DELETE tblUserFriendBlackList WHERE UserId=@UserId GO
create table Employee ( ID INT IDENTITY(1,1) CONSTRAINT pk_tbid PRIMARY KEY, Name NVARCHAR(10), Sex BIGINT, Brothday DATETIME ) --假设还有一个复杂的财务表关联Employee --当添加完员工信息后,循环读取财务信息初始所有化员工财务(假设) CREATE PROC Employeeinsert ( @Name NVARCHAR(10), @Sex BIGINT, Brothday DATETIME ) AS BEGIN BEGIN TRAN --事务 INSERT INTO Employee (Name,Sex,Brothday) VALUES (@Name,@Sex,@Brothday) IF @@ERROR<>0 BEGIN ROLLBACK TRAN ENDDECLARE @ID INT/*************************游标***************************/ DECLARE cur_cw CURSOR FOR SELECT ID FROM Employee OPEN cur_cw FETCH NEXT FROM cur_cw INTO @ID WHILE(@@FETCH_STATUS=0) BEGIN --操作员工财务的代码,我这里是根据员工ID来关联 FETCH NEXT FROM cur_cw INTO @ID END CLOSE cur_cw DEALLOCATE cur_cw /**********************************************************/ IF @@ERROR<>0 BEGIN ROLLBACK TRAN ENDCOMMIT TRAN END GO
create table Employee ( ID INT IDENTITY(1,1) CONSTRAINT pk_tbid PRIMARY KEY, Name NVARCHAR(10), Sex BIGINT, Brothday DATETIME ) --假设还有一个复杂的财务表关联Employee --当添加完员工信息后,循环读取财务信息初始所有化员工财务(假设) CREATE PROC Employeeinsert ( @Name NVARCHAR(10), @Sex BIGINT, Brothday DATETIME ) AS BEGIN BEGIN TRAN --事务 INSERT INTO Employee (Name,Sex,Brothday) VALUES (@Name,@Sex,@Brothday) IF @@ERROR<>0 BEGIN ROLLBACK TRAN ENDDECLARE @ID INT/*************************游标***************************/ DECLARE cur_cw CURSOR FOR SELECT ID FROM Employee OPEN cur_cw FETCH NEXT FROM cur_cw INTO @ID WHILE(@@FETCH_STATUS=0) BEGIN --操作员工财务的代码,我这里是根据员工ID来关联 FETCH NEXT FROM cur_cw INTO @ID END CLOSE cur_cw DEALLOCATE cur_cw /**********************************************************/ IF @@ERROR<>0 BEGIN ROLLBACK TRAN ENDCOMMIT TRAN END GO
create table Employee ( ID INT IDENTITY(1,1) CONSTRAINT pk_tbid PRIMARY KEY, Name NVARCHAR(10), Sex BIGINT, Brothday DATETIME ) --假设还有一个复杂的财务表关联Employee --当添加完员工信息后,循环读取财务信息初始所有化员工财务(假设) CREATE PROC Employeeinsert ( @Name NVARCHAR(10), @Sex BIGINT, Brothday DATETIME ) AS BEGIN BEGIN TRAN --事务 INSERT INTO Employee (Name,Sex,Brothday) VALUES (@Name,@Sex,@Brothday) IF @@ERROR<>0 BEGIN ROLLBACK TRAN ENDDECLARE @ID INT/*************************游标***************************/ DECLARE cur_cw CURSOR FOR SELECT ID FROM Employee OPEN cur_cw FETCH NEXT FROM cur_cw INTO @ID WHILE(@@FETCH_STATUS=0) BEGIN --操作员工财务的代码,我这里是根据员工ID来关联 FETCH NEXT FROM cur_cw INTO @ID END CLOSE cur_cw DEALLOCATE cur_cw /**********************************************************/ IF @@ERROR<>0 BEGIN ROLLBACK TRAN ENDCOMMIT TRAN END GO
大家都生活在IDE的环境,
(@usrName varchar(30), @usrPasswd varchar(30),@age int, @PhoneNum varchar(20), @Address varchar(50) )
as
begin
//显示定义并开始一个事务
begin tran
insert into USER(userName,userPasswd) values(@usrName,@usrPasswd)
if @@error<>0
begin
//操作失败,则事务回滚
rollback tran
//返回存储过程,并设置返回码为事务操作失败
return -1
end
insert into USERDOC(userName,age,sex,PhoneNumber,Address)
values(@Usrname,@age,@PhoneNum,@Address)
if @@error<>0
begin
//操作失败,则事务回滚
rollback tran
return -1
end
//如果操作执行正确,则提交事务
commit tran
return 0
end
USE [Unigo.Com]
GO
DECLARE @UserId int
SET @UserId=1144
DECLARE @UserId_tmp int
DECLARE @MsgId_tmp int
DECLARE DELETE_MESSAGE CURSOR FOR
SELECT user_to_p_msg.user_id ,p_msg.private_message_id FROM
[dbo].[p_msg]
INNER JOIN
[dbo].user_to_p_msg
ON
[dbo].[p_msg].private_message_id =
[dbo].user_to_p_msg.private_message_id
where [dbo].[p_msg].user_id =@UserId
OPEN DELETE_MESSAGE
FETCH NEXT FROM DELETE_MESSAGE
INTO @UserId_tmp,@MsgId_tmp
WHILE @@FETCH_STATUS = 0
BEGIN
Print '---'+ cast(@UserId_tmp as varchar)+'---'+cast(@MsgId_tmp as
varchar)
EXEC [dbo].[Unigo_Community_DeletePrivateMessage] @UserId_tmp,@MsgId_tmp
FETCH NEXT FROM DELETE_MESSAGE INTO @UserId_tmp,@MsgId_tmp
END
CLOSE DELETE_MESSAGE
DEALLOCATE DELETE_MESSAGE
-----DELETE Two
DECLARE DELETE_MESSAGE_2 CURSOR FOR
SELECT
user_to_p_msg.user_id,p_msg.private_message_id
FROM
[dbo].[p_msg]
LEFT OUTER JOIN
[dbo].user_to_p_msg
ON
[dbo].[p_msg].private_message_id =
[dbo].user_to_p_msg.private_message_id
where [dbo].user_to_p_msg.user_id =@UserId
OPEN DELETE_MESSAGE_2
FETCH NEXT FROM DELETE_MESSAGE_2
INTO @UserId_tmp,@MsgId_tmp
WHILE @@FETCH_STATUS = 0
BEGIN
Print '---'+ cast(@UserId_tmp as varchar)+'---'+cast(@MsgId_tmp as
varchar)
EXEC [dbo].[Unigo_Community_DeletePrivateMessage] @UserId_tmp,@MsgId_tmp
FETCH NEXT FROM DELETE_MESSAGE_2 INTO @UserId_tmp,@MsgId_tmp
END
CLOSE DELETE_MESSAGE_2
DEALLOCATE DELETE_MESSAGE_2
IF @@ERROR=0
BEGIN
--Delete P_MSG Info
delete P_MSG where user_id=@UserId
--Delete UserFriendBlackList
END
GO
USE [Unigo]
GO
DECLARE @UserId int
SET @UserId=1144
DELETE tblUserFriendBlackList WHERE UserId=@UserId
GO
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tbid PRIMARY KEY,
Name NVARCHAR(10),
Sex BIGINT,
Brothday DATETIME
)
--假设还有一个复杂的财务表关联Employee
--当添加完员工信息后,循环读取财务信息初始所有化员工财务(假设)
CREATE PROC Employeeinsert
(
@Name NVARCHAR(10),
@Sex BIGINT,
Brothday DATETIME
)
AS
BEGIN
BEGIN TRAN --事务
INSERT INTO Employee (Name,Sex,Brothday) VALUES (@Name,@Sex,@Brothday)
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ENDDECLARE @ID INT/*************************游标***************************/
DECLARE cur_cw CURSOR FOR
SELECT ID FROM Employee
OPEN cur_cw
FETCH NEXT FROM cur_cw INTO @ID
WHILE(@@FETCH_STATUS=0)
BEGIN
--操作员工财务的代码,我这里是根据员工ID来关联
FETCH NEXT FROM cur_cw INTO @ID
END
CLOSE cur_cw
DEALLOCATE cur_cw
/**********************************************************/
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ENDCOMMIT TRAN
END
GO
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tbid PRIMARY KEY,
Name NVARCHAR(10),
Sex BIGINT,
Brothday DATETIME
)
--假设还有一个复杂的财务表关联Employee
--当添加完员工信息后,循环读取财务信息初始所有化员工财务(假设)
CREATE PROC Employeeinsert
(
@Name NVARCHAR(10),
@Sex BIGINT,
Brothday DATETIME
)
AS
BEGIN
BEGIN TRAN --事务
INSERT INTO Employee (Name,Sex,Brothday) VALUES (@Name,@Sex,@Brothday)
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ENDDECLARE @ID INT/*************************游标***************************/
DECLARE cur_cw CURSOR FOR
SELECT ID FROM Employee
OPEN cur_cw
FETCH NEXT FROM cur_cw INTO @ID
WHILE(@@FETCH_STATUS=0)
BEGIN
--操作员工财务的代码,我这里是根据员工ID来关联
FETCH NEXT FROM cur_cw INTO @ID
END
CLOSE cur_cw
DEALLOCATE cur_cw
/**********************************************************/
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ENDCOMMIT TRAN
END
GO
create table Employee
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tbid PRIMARY KEY,
Name NVARCHAR(10),
Sex BIGINT,
Brothday DATETIME
)
--假设还有一个复杂的财务表关联Employee
--当添加完员工信息后,循环读取财务信息初始所有化员工财务(假设)
CREATE PROC Employeeinsert
(
@Name NVARCHAR(10),
@Sex BIGINT,
Brothday DATETIME
)
AS
BEGIN
BEGIN TRAN --事务
INSERT INTO Employee (Name,Sex,Brothday) VALUES (@Name,@Sex,@Brothday)
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ENDDECLARE @ID INT/*************************游标***************************/
DECLARE cur_cw CURSOR FOR
SELECT ID FROM Employee
OPEN cur_cw
FETCH NEXT FROM cur_cw INTO @ID
WHILE(@@FETCH_STATUS=0)
BEGIN
--操作员工财务的代码,我这里是根据员工ID来关联
FETCH NEXT FROM cur_cw INTO @ID
END
CLOSE cur_cw
DEALLOCATE cur_cw
/**********************************************************/
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ENDCOMMIT TRAN
END
GO