Stored Procedure with output parameter being the Id of the last insertion. C#

Question

Hello, I’m performing an insert in a table, but I need it to return the ID you just entered. I searched on the command OUTPUT but I couldn’t solve my problem. I need to do all this using procedures, IE, I also need to know how to "catch" this return from the process.

Procedure:

CREATE PROCEDURE SaveProfessionalUser
@id int,
@title varchar(50)

AS
BEGIN       
    INSERT INTO ProfessionalType VALUES (@title)
END

Codebehind:

using (_context)
{
    SqlCommand cmd = new SqlCommand("SaveProfessionalUser");
    cmd.Parameters.AddWithValue("@idProfessionalUser", pUser.IdProfessionalUser);
    cmd.Parameters.AddWithValue("@title", pUser.Title);
    cmd.CommandType = CommandType.StoredProcedure;
    this._context.ExecuteProcedure(cmd);

    return true;
}

Answer 1

Open another command and use the following:

SELECT IDENT_CURRENT ('ProfessionalUser') AS UltimoIdInserido;

There are other alternatives, such as @@IDENTITY and SCOPE_IDENTITY, but they are relative to all tables or the whole session, which is not very safe.

If it were a query normal, there is also the statement OUTPUT, but how are you running a PROCEDURE, the IDENT_CURRENT is the ideal.

This way you’re wearing is a little weird, but it stays like this:

using (_context)
{
    SqlCommand cmd = new SqlCommand("SaveProfessionalUser");
    cmd.Parameters.AddWithValue("@idProfessionalUser", pUser.IdProfessionalUser);
    cmd.Parameters.AddWithValue("@title", pUser.Title);
    cmd.CommandType = CommandType.StoredProcedure;
    this._context.ExecuteProcedure(cmd);

    int lastIdInserted = Convert.ToInt32(cmd.ExecuteScalar());

    return true;
}

EDIT

As requested in comment, if it is the PROCEDURE that returns the value, would look like this:

CREATE PROCEDURE SaveProfessionalUser
@id int,
@title varchar(50)

AS
BEGIN       
    INSERT INTO ProfessionalType VALUES (@title)
    RETURN SCOPE_IDENTITY()
END

Applying:

using (_context)
{
    SqlCommand cmd = new SqlCommand("SaveProfessionalUser", _context.Connection);
    cmd.Parameters.AddWithValue("@idProfessionalUser", pUser.IdProfessionalUser);
    cmd.Parameters.AddWithValue("@title", pUser.Title);
    cmd.CommandType = CommandType.StoredProcedure;
    int ultimoIdInserido = (Int32)cmd.ExecuteScalar().ToString();

    return true;
}

Answer 2

A form used by Entity Framework and running with @@ROWCOUNT And scope_identity().

ALTER PROCEDURE SaveProfessionalUser
@id int,
@title varchar(50)

AS
BEGIN       
    INSERT INTO ProfessionalType VALUES (@title);
    SELECT id FROM ProfessionalType WHERE @@ROWCOUNT > 0 AND id = scope_identity();
END

In relation to scope_identity, identity and ident_current, the site says: Returns the last identity value inserted in an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two instructions will be in the same scope if they are in the same stored procedure, function or batch (Source and Credit: http://technet.microsoft.com/pt-br/library/ms190315.aspx (translation)), ie if you do any of the three and they are within the same procedure (Procedure) will work the same way, rescuing the last inserted element.

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns. (Source and Credit: http://technet.microsoft.com/pt-br/library/ms190315.aspx (translating))

With this example of Procedure that I posted would be your code more or less like this, being that you are using Sqlclient (Sqlconnection and Sqlcommand)?

int idInserido = 0;
using (_context)
{
    SqlCommand cmd = new SqlCommand("SaveProfessionalUser");
    cmd.Parameters.AddWithValue("@idProfessionalUser", pUser.IdProfessionalUser);
    cmd.Parameters.AddWithValue("@title", pUser.Title);
    cmd.CommandType = CommandType.StoredProcedure;
    idInserido = int.Parse(this._context.ExecuteScalar().ToString());

    return true;
}

References:

• SCOPE_IDENTITY (Transact-SQL)

• @@ROWCOUNT (Transact-SQL)