My easy and simple way to raise an error in T-SQL (4/21/2010 updated)

Sometimes I need to know if a database operation is success or failure in my code (not in stored procedures) and perform different behaviors. My easy and simple way is raising an error in stored procedures and return a specific error message to my application, and I will check if that message is presented in the returning result. The syntax is:
Begin Try
  Update xxxx
  Set yy = zzz
  Where aa = 123
  Select @updateRowcount = @@Rowcount, @ErrorCode = @@Error

  If (@updateRowcount = 0)
    Raiserror('0 row(s) affected', 11, 1)
  If (@ErrorCode != 0)
    Raiserror('Update failed.', 11, 1)

End Try

Begin Catch
  Select Error_number(), Error_line(), Error_message()
End Catch
The string in the Raiserror() function is the "specific message".

No comments:

Post a Comment