首页 新闻 会员 周边

存储过程带事务，如何返回值，请给个详细的例子

0
悬赏园豆：5 [已解决问题] 解决于 2012-04-25 12:17

如题：存储过程带事务，如何返回值，请给个详细的例子

xu_happy_you的主页 xu_happy_you | 菜鸟二级 | 园豆：222
提问于：2012-04-25 09:29
< >
分享
最佳答案
0 
/*****Begin******/
/*
-- StoredProcedure Name: SP_ADInsert
-- Date Generated: 2012/4/25 9:59:08
*/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SP_ADInsert]') AND type='P')
DROP PROCEDURE [dbo].[SP_ADInsert]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SP_ADInsert]
(
    @CustomerName nvarchar(200),
    @LoginID nvarchar(50),
    @AddTime datetime,
    @CurState smallint,
    @ReTurnValue int OUTPUT    
)
AS

BEGIN TRAN

    Insert
        [AD]
        (
            [CustomerName],
            [LoginID],
            [AddTime],
            [CurState]
        )    
        Values
        (
            @CustomerName,
            @LoginID,
            @AddTime,
            @CurState
        )

    set @ReTurnValue=SCOPE_IDENTITY()

IF @@ERROR!=0
    BEGIN
        set @ReTurnValue=-1
        ROLLBACK
    END
ELSE
    BEGIN        
        COMMIT
    END

GO
收获园豆：5
邀月 | 高人七级 |园豆：25475 | 2012-04-25 11:21

我的需求是删除一条新闻，会把留言删掉，留言的回复内容也要删掉，就是要操作3个表，因为不想在DAL层写带事务的删除会好麻烦，所以想直接写过存储过程，如下是我今天写的，不知道可行不，你帮我看看！

ALTER PROCEDURE [dbo].[proc_tb_news_delete]
( 
    @newsID INT,
    @record TINYINT OUTPUT
)    
AS
BEGIN
    DECLARE @leavewordCount INT --留言个数
    DECLARE @delete_where VARCHAR(4000) --留言id字符，类似1,2,4,5,6
    SET @leavewordCount=(SELECT ISNULL(COUNT(1),0) FROM tb_leaveword WHERE newsID=@newsID)
    SET @delete_where=''

    IF(@leavewordCount=0) --此条新闻无留言时
        BEGIN TRY
            DELETE FROM tb_news WHERE newsID=@newsID
            SET @record=0 --成功
        END TRY
        BEGIN CATCH
            SET @record=-1 --失败
        END CATCH

    ELSE IF(@leavewordCount>0) --此条新闻有留言时
       ----获取删除条件----
       DECLARE MY_CURSOR CURSOR
       FOR SELECT leavewordID FROM tb_news WHERE newsID=@newsID
       BEGIN
           DECLARE @leavewordID INT
           OPEN MY_CURSOR
           FETCH NEXT FROM MY_CURSOR INTO @leavewordID
           IF(@leavewordID IS NOT NULL)
               SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','
               WHILE(@@FETCH_STATUS<>-1)
                   BEGIN
                       SET @leavewordID=NULL
                       FETCH NEXT FROM MY_CURSOR INTO @leavewordID
                       IF(@leavewordID IS NOT NULL)
                           SET @delete_where=@delete_where+CAST(@leavewordID AS VARCHAR(10))+','
                   END
        END
        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR
        SET @delete_where=SUBSTRING(@delete_where,1,LEN(@delete_where)-1)
        ----获取删除条件(end)----
        BEGIN
            BEGIN TRY
                BEGIN TRAN
                    DELETE FROM tb_news WHERE newsID=@newsID
                    EXECUTE('DELETE FROM tb_leaveword WHERE leavewordID IN('+@delete_where+')')
                    EXECUTE('DELETE FROM tb_reply WHERE leavewordID IN('+@delete_where+')')
                    SET @record=0 --成功
                    COMMIT
            END TRY
            BEGIN CATCH
                ROLLBACK
                SET @record=-1
            END CATCH
        END

END
xu_happy_you | 园豆：222 (菜鸟二级) | 2012-04-25 11:46

你写了最详细了，满意答案选你的了

xu_happy_you | 园豆：222 (菜鸟二级) | 2012-04-25 12:17
其他回答(2)
0

使用输出参数不能解决问题？

无之无 | 园豆：5095 (大侠五级) | 2012-04-25 11:13

见我对2楼的回复，帮我看看

支持(0) 反对(0) xu_happy_you | 园豆：222 (菜鸟二级) | 2012-04-25 11:47
0

可以的，在外层判断@record的值。

小材小用 | 园豆：639 (小虾三级) | 2012-04-25 12:10
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。