如何知道执行的TSQL存储过程更新

How can I check if my TSQL stored procedure updated within the stored procedure in order to create a proper message?

如何检查我的TSQL存储过程是否在存储过程中更新以创建正确的消息？

Example:

例：

ALTER PROCEDURE [dbo].[pUpdate]
            @id uniqueidentifier, 
            @status int,
            @message VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [database].[dbo].[user]
    SET status = @status
    WHERE Id = @id
END

IF (SUCCESSFUL)
BEGIN
    @message = 'Success!'
END

What are some possible ways to check if successful without using the parameters again?

有什么方法可以在不使用参数的情况下检查是否成功？

This is what I currently use:

这是我目前使用的：

  SELECT COUNT(*)
    WHERE status = @status AND id = @id

Are there any other ways? I want to know for my knowledge and reference. Thanks.

还有其他方法吗？我想知道我的知识和参考。谢谢。

4 个解决方案

#1

Have you checked out @@ROWCOUNT? Might be what you're looking for (see this for details: http://technet.microsoft.com/en-us/library/ms187316.aspx). Basically it returns the number of rows affected by the last statement. I'd imagine if it were not "successful", it would be zero rows.

你有没看过@@ ROWCOUNT？可能是您正在寻找的（有关详细信息，请参阅此处：http：//technet.microsoft.com/en-us/library/ms187316.aspx）。基本上它返回受最后一个语句影响的行数。我想如果它不是“成功”，它将是零行。

#2

ALTER PROCEDURE [dbo].[pUpdate]
            @id uniqueidentifier, 
            @status int,
            @message VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [database].[dbo].[user]
    SET status = @status
    WHERE Id = @id
END

IF (@@ROWCOUNT > 0)
BEGIN
     @message = 'Success!'
END
ELSE 
BEGIN
    @message = 'Not success!'
END

#3

You can use a try catch block and log the success or failure to a table.

您可以使用try catch块并将成功或失败记录到表中。

BEGIN TRY
    BEGIN TRANSACTION
    -- Add Your Code Here
    -- Log Success to a log table
    COMMIT
END TRY
BEGIN CATCH
    -- Log failure to a log table
    ROLLBACK
END CATCH

#4

I would use @@ERROR system variable to check whether the last sentence was successfull (error # = 0) or not (error # > 0 ):

我会使用@@ ERROR系统变量检查最后一句是否成功（错误＃= 0）或不是（错误＃> 0）：

USE Database;
GO

BEGIN
    UPDATE TableName
    SET ColumnA = 4
    WHERE ColumnB = 1;
END

IF (@@ERROR = 0)
BEGIN
    PRINT N'Successfull Update';
    GO
END

You can go deeper into Microsoft MSDN here: http://technet.microsoft.com/es-es/library/ms188790.aspx

您可以在此处深入了解Microsoft MSDN：http：//technet.microsoft.com/es-es/library/ms188790.aspx

#1

#2

ALTER PROCEDURE [dbo].[pUpdate]
            @id uniqueidentifier, 
            @status int,
            @message VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [database].[dbo].[user]
    SET status = @status
    WHERE Id = @id
END

IF (@@ROWCOUNT > 0)
BEGIN
     @message = 'Success!'
END
ELSE 
BEGIN
    @message = 'Not success!'
END

#3