How do I retrieve scope identity with ExecuteNonQuery?

December 4, 2021

My project is using .NET Core 3.1 and I have my stored procedures executing in my repository class. I want to insert and return the scope identity(the id of the record that just inserted UserNumber) so I can use it for another stored proc within this same method. The problem I have here is that parameters[1].Value value is returning zero.

Here is an abbreviation of my stored proc:

ALTER PROCEDURE [dbo].[InsertUser]
@iUserNumber            int OUTPUT,
As
    INSERT dbo.tblUser (
        CreatedBy
        )
    VALUES          (@LoginUserId)
IF @@ERROR <> 0 GOTO ERRHANDLER
SET @UserNumber = SCOPE_IDENTITY() /** this is the primary Key **/
RETURN(@UserNumber)

Here is a sample of my repository

public int InsertUsers(int LoginUserId, int UserNumber)
{
    SqlParameter[] parameters = new List<SqlParameter>()
    {
            _dbContext.CreateSqlParameter(StoredProcedureConstants.LoginUserId,SqlDbType.Int,LoginUserId.ToSafeInt()),
            _dbContext.CreateSqlParameter(StoredProcedureConstants.UserNumber,SqlDbType.Int,UserNumber.ToSafeInt())                
        }.ToArray();
        var intResult = _dbContext.ExecuteNonQuery(StoredProcedureConstants.InsertUsers, parameters);
        var result2 = parameters[1].Value; //This comes back as zero

How do I assign the scope identity to result2?

>Solution :

Should be something like:

CREATE OR ALTER PROCEDURE [dbo].[InsertUser]  
           @LoginUserId int,
           @iUserNumber int OUTPUT
As
    INSERT dbo.tblUser (
        CreatedBy
        )
    VALUES          (@LoginUserId)
    SET @iUserNumber = SCOPE_IDENTITY() /** this is the primary Key **/

and

SqlParameter[] parameters = new List<SqlParameter>()
{
   _dbContext.CreateSqlParameter("@LoginuserId",SqlDbType.Int,LoginUserId),
   _dbContext.CreateSqlParameter("@iUserNumber",SqlDbType.Int)                
 }.ToArray();
 parameters[1].Direction = ParameterDirection.Output;

 _dbContext.ExecuteNonQuery(StoredProcedureConstants.InsertUsers, parameters);
 var result2 = parameters[1].Value;