Problem with SCOPE_IDENTITY() in ADO.NET

I’m using .NET 3.5, Visual Studio Team System 2008 and SQL Server 2005 and i’m trying to return the new id that is created after an INSERT statement. When i call ExecuteScalar, it returns zero. I’ve googled the problem and it seems that you need to cast the scope_identity to int before returning it.

Here are 3 solotions for the problem:

1. SELECT CAST(SCOPE_IDENTITY() AS INT) instead of just SELECT SCOPE_IDENTITY()

2. Declare a variable to hold the scope identity as follows:

DECLARE @NewID INT

SET @NewID =  SELECT SCOPE_IDENTITY()

3. Have an output parameter in your stored procedure

comments powered by Disqus