Getting the value of the last identity field after insert

Often times you want to insert a row into a SQL table that has an identity column and then use the the value of the indentity for the new row in other statements. There are a couple ways to do this in SQl Server.

Insert into tblCustomer (CustomerName, Address) values ('Customer A', 'Some city')

DECLARE @CustID as integer
SET @CustID = @@IDENTITY     --  this is not bound by scope

SET @CustID = SCOPE_IDENTITY()    -- applies only to the scope of the current piece of code, ie stored procedure, trigger, etc.