存储过程中的sql多次更新

Question

在存储过程中使用多个更新语句时，处理返回值的最佳方式是什么。

我在数据适配器中使用这个存储过程，并试图获得更新结果总数。

我正在使用 SQL Server 2008 和 .NET 4.5

例如:

CREATE PROCEDURE [dbo].[usrsp_MessageConversation_delete]
(
    @fromMbrID int,
    @toMbrID int
)
AS
BEGIN
SET NOCOUNT OFF;
UPDATE mbr_Messages
SET  fromDeleted = 1
WHERE (fromMbrID = @fromMbrID) AND (toMbrID = @toMbrID)

UPDATE mbr_Messages
    SET toDeleted = 1
WHERE (fromMbrID = @toMbrID) AND (toMbrID = @fromMbrID)

//RETURN TOTAL NUMBER OF UPDATED RECORDS
    EG: RETURN  @@ROWCOUNT ??

END

我想返回更新记录的总数，如果没有更新则返回 0。

我是否需要添加任何其他错误处理 - 如果有任何建议？

更新:我最终选择了这个:

BEGIN

SET NOCOUNT ON;

DECLARE @rowCounter INT;

BEGIN TRY 

    UPDATE 
        mbr_Messages
    SET 
        fromDeleted = 1
    WHERE 
        (fromMbrID = @fromMbrID) AND (toMbrID = @toMbrID) AND (fromDeleted = 0);

    SET @rowCounter = @@ROWCOUNT;

    UPDATE 
        mbr_Messages
    SET 
        toDeleted = 1
    WHERE 
        (fromMbrID = @toMbrID) AND (toMbrID = @fromMbrID) AND (toDeleted = 0);

    SET @rowCounter = @rowCounter + @@ROWCOUNT;

END TRY
BEGIN CATCH
    PRINT 'Update failed';
END CATCH 

RETURN @rowCounter

END

Answer 1

你想做如下的事情:

CREATE PROCEDURE [dbo].[usrsp_MessageConversation_delete]
(
    @fromMbrID int,
    @toMbrID int
)
AS
BEGIN
SET NOCOUNT OFF;
    DECLARE @RowCount1 INTEGER
    DECLARE @RowCount2 INTEGER
    DECLARE @TotalRows INTEGER

UPDATE mbr_Messages
SET  fromDeleted = 1
WHERE (fromMbrID = @fromMbrID) AND (toMbrID = @toMbrID)

SET @RowCount1=@@RowCount

UPDATE mbr_Messages
    SET toDeleted = 1
WHERE (fromMbrID = @toMbrID) AND (toMbrID = @fromMbrID)

SET @RowCount2=@@RowCount

SET @TotalRows = @RowCount1 + @RowCount2

--RETURN TOTAL NUMBER OF UPDATED RECORDS
  RETURN  @TotalRows

END

您需要将@@RowCount 分配给某个变量，因为它会在您使用后重置。

编辑:

同时添加错误处理代码:Try..Catch 和 Transactions。