Tuesday, May 05, 2009

Can't use SCOPE_IDENTITY() to return uniqueidentifier id

SCOPE_IDENTITY() returns the last generated *identity* value in the current scope (function, stored proc., trigger, etc.). Identity columns need to be of type bigint, int or smallint and are not compatible with type uniqueidentifier (i.e. you can't assign an integer to a variable defined as a uniqueidentifier).

So, if you're asking SQL Server to give you last identity inserted and then assigning that to a variable previously declared as a uniqueidentifier, you'll end up with the compatibility error that you're receiving.