Stored Procedure with output parameter being the Id of the last insert. W#

Navigation
2

Hello, I'm performing an insert into a table, but I need it to return the ID you just entered. I searched the OUTPUT command but could not solve my problem. I need to do all this using procedures, ie I also need to know how to "get" this return from the procedure.

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;
}
    
asked by anonymous 13.05.2014 / 21:44

2 answers

2

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 entire session, which is not very secure.

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

This way you're using is a little weird, but it looks 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, it would look like this: 

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

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

Application: 

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;
}
    
13.05.2014 / 21:51
1

A form used by the Entity Framework and running with @@ROWCOUNT E 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

Regarding scope_identity , identity and ident_current , the site says: Returns the last identity value entered in an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements will be in the same scope if they are in the same stored procedure, function, or batch ( Source and Credit : link , that is, if you do any of the three and they are inside the same 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 ): link (English translation)

With this example of Procedure that I posted would your code be more or less like this, being you 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:

13.05.2014 / 22:00
Add input button num select Extract data from an array in javascript