How to find out how an update to a TSQL stored procedure is completed

Question

How to check if the TSQL stored procedure is updated in the stored procedure to create the correct message?

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 the possible ways to verify success without reusing parameters?

This is what I am using now:

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

Are there any other ways? I want to know my knowledge and recommendations. Thank you

Answer 1

Did you check @@ROWCOUNT ? Perhaps this is what you are looking for (for more details see: http://technet.microsoft.com/en-us/library/ms187316.aspx ). This basically returns the number of rows affected by the last statement. I would suggest that if it were not “successful,” it would be zero lines.

Answer 2

You can use the catch try block and record the success or failure of the table.

 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 

Answer 3

 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 

Answer 4

I would use the @@ERROR system variable to check if the last sentence was successful (error # = 0) or not (error #> 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 get deeper into Microsoft MSDN here: http://technet.microsoft.com/es-es/library/ms188790.aspx