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
?
Advertisement
Answer
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;