sql2005 存储过程使用 事务 和 游标 例子

　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