Code Luck

@@IDENTITY will return the last identity value entered into a table in your current session (this is limited to your session only, so you won't get identities entered by other users). While @@IDENTITY is limited to the current session, it is not limited to the current scope. In other words, if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. Now this isn't bad, as long as you ensure that things are done in the correct order. Where this can get ugly is when there is an application revision and a new trigger gets added that gets fired from your stored procedure. Your code didn't anticipate this new trigger, so you could now be getting an incorrect value back.

SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

2. Prefix Stored Procedures with "sp_"

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

1. The stored procedure in the master database.
   
2. The stored procedure based on any qualifiers provided (database name or owner).
   
3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

I took time and broke the tradition of prefixing the stored procs with sp_ and renamed all my stored procs by removing the prefix, also replaced @@IDENTITY with SCOPE_IDENTITY() for future maintainance purposes.

For Pros these might be 101s, but his article explained by exactly not to do some things, rather than just putting it down as a rule on the holy grail.