SQLSERVER2005存儲過程事務，遊標實例

存儲過程事務
alter proc [dbo]。[Unigo _ community _ deleteprivatemessage]
@ userId int，
@ privateMessageId int
as
BEGIN
IF(@ userId > 0且@ privateMessageId >0)
BEGIN
BEGIN trans _ MSG
BEGIN
delete from user _ to _ P _ MSG
其中user_id = @userId
和private _ privatecom]
GO
DECLARE @ UserId int
SET @ UserId = 1144
DECLARE @ UserId _ tmp int
DECLARE @ MsgId _ tmp int
DECLARE DELETE _ MESSAGE遊標FOR
SELECT user _ to _ p _ msg . user _ id，p _ msg . private _ MESSAGE _ id FROM
[dbo]。[p_msg]
內部聯接
[dbo]。user _ to _ p _ msg
ON
[dbo]。[p_msg]。private_message_id =[/ br/] [dbo]。user _ to _ p _ msg . private _ message _ id
其中[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兩個
DECLARE DELETE_MESSAGE_2遊標FOR
SELECT
user _ to _ p _ msg . user _ id，p_msg.private[p_msg]
左外部聯接
[dbo]。user _ to _ p _ msg
ON
[dbo]。[p_msg]。private_message_id =
[dbo]。user _ to _ p _ msg . private _ message _ id
其中[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)[/br[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其中user _ id = @

位律師廻複