SQL SERVER2005 存储过程事务，游标实例（测试通过）

最近记性比较差，记录一下 呵呵~~

存储过程事务

ALTER proc [dbo].[Unigo_Community_DeletePrivateMessage]
@userId int,
@privateMessageId int
as
begin
 if(@userId >0 and @privateMessageId >0)
 begin
BEGIN TRANSACTION Tran_MSG
BEGIN
  delete from user_to_p_msg
  where user_id = @userId
  and private_message_id = @privateMessageId
delete from P_MSG where user_id=@userId and private_message_id =@privateMessageId
END

IF (@@ERROR<>0)
ROLLBACK TRANSACTION Tran_MSG
ELSE
COMMIT TRANSACTION Tran_MSG
 end
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

 

附参考文章：讲的比较全面如事务三种运行模式等

网址：http://hi.baidu.com/ella_qing/blog/item/5a0c5eb5047123ce36d3cab7.html