Avinashsing Sunkur Blog

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