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()
  return
End Catch

The string in the Raiserror() function is the "specific message".

Wrapping statements

Many people (me also) like to wrap many statements into a single statement so that it looks short and clean. However, it's not a good idea in the following LINQ to SQL query:

string[] names = { "Jim", "John", "Kenny", "Mary", "Jay" };

IEnumerable<string> query = 
from n in names
where n.Length == names.Max(n1 => n1.Length)
select n;  //the output is Kenny

This query is not so efficiency because the subquery is recalculated on each outer loop and will create one more round trip to the database each time. We can improve it by separating the subquery like this:

int max = names.Max (n => n.Length);

IEnumerable<string> query = 
from n in names
where n.Length == max
select n;

PS: If you want to understand more, please refer to the C# 3.0 in a nutshell.