SQLSERVER2005存儲過程事務(wù)，游標(biāo)實(shí)例

存儲過程事務(wù)
    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　　
    游標(biāo)實(shí)例
    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