Often times as a developer we all need to insert new records into a table in a database and also retrieve the identity id of the most recently added row. The most common solution to this situation is use @@Identity. Simple eh? No, because @@Identity returns the most recently created identity for your connection. So if there is any other operation going on like a trigger, the identity created by the trigger would be returned instead of yours.
So it is better to use scope_identity() always.
Naming stored procedure with a prefix of sp_ is pretty common, i should say that is the current norm. But Apparently this is a bad habbit because the system databases are the only ones that should be named with prefix sp_. Long story short, SQL server starts looking for any stored procedure with a prefix sp_ in the Master database.
For more on the above feature, go here . Pretty cool dont's info on SQL practices
