SCOPE_IDENTITY: Recoger un identificador recién insertado

Os confieso que he estado más tiempo pensando en el título del artículo que en el artículo en sí... y como me parece que de autodescriptivo no tiene nada, espero poder explicarme en el siguiente párrafo.

Comúnmente, cuando creamos una tabla en una base de datos, uno de los campos suele ser un entero que se incrementa automáticamente cada inserción de registro, de modo que no puede haber dos enteros autoincrementales iguales. Esta propiedad la solemos utilizar para nombrar a ese campo como clave principal de la base de datos, dado que estamos totalmente seguros de que es única, y en definitiva es un entero que ocupa poco espacio y es fácilmente indexable.

Personalmente he oído llamar a esos campos de diferentes modos: autoincrementales, autonuméricos, identities... el nombre da igual, lo importante es que sepamos de lo que hablamos.

Pero a lo que vamos, len este artículo trataremos el escenario en que el programador necesita saber cuál es ese autonumérico recién insertado. Un ejemplo típico es el de realizar la inserción en base de datos e inmediatamente redireccionar a una página con la información recién insertada, para la que necesitamos saber el autonumérico que la identifica.

Estoy seguro de que a muchos de vosotros se os ha planteado esta situación... y estoy seguro de que muchos de vosotros habéis cometido el error (¡¡yo mismo lo hacía en su momento!!) de:
1.- Insertar en base de datos.
2.- Hacer un SELECT que recoge el último registro insertado para saber cuál es su autonumérico.

Esto no sólo supone dos viajecitos de ida y vuelta a la Base de Datos (lo cual en momentos de exceso de estrés porculiano podemos suponer aceptable ) sino que corremos el riesgo real de que dos usuarios hagan sendas inserciones y que ambos SELECT devuelvan el mismo autonumérico...

Pero bueno, no hace falta explicaciones, hacerlo así es una basura y absolutamente irrecomendable .

El modo de proceder en estos casos es el siguiente:
1.- El procedimiento de datos que realiza la inserción debe devolver el SCOPE_IDENTITY().
2.- El código que ejecuta la llamada al procedimiento almacenado debe recoger el valor devuelto por el procedimiento alamcenado.

Sí, ya lo sé, la teoría es muy bonita, pero un ejemplo vale más que mi palabras, así que vayamos por partes:

1.- El procedimiento almacenado.
Dando por supuesto que se va a realizar la inserción en una tabla con un registro autonumérico. no hay más que devolver como valor de retorno el SCOPE_IDENTITY(). Ejemplo de procedimiento almacenado:

    CREATE PROCEDURE dbo.InsertaEjemplo
    (
        @T_Titulo nvarchar(50),
        @T_Texto nvarchar(MAX)
    )

    AS
        SET NOCOUNT ON
   
    INSERT INTO TablaEjemplo
                      (T_Titulo , T_Texto)
    VALUES     (@T_Titulo,@T_Texto)

    RETURN SCOPE_IDENTITY()


2.- Método de capa DAL
A continuación tenemos un método de la capa DAL a la que sólo se le entregan dos campos.

    public static int InsertarEjemplo(string Titulo, string Texto)
    {
        // Valor de retorno. Sabemos que si acaba valiendo -1 ha habido un error
        int Id = -1;

        // Parametros de la BBDD
        SqlParameter[] sqls = new SqlParameter[2];
        sqls[0] = new SqlParameter("T_Titulo", Titulo);
        sqls[1] = new SqlParameter("T_Texto", Texto);

        // Se puede recoger la ConnectionString de mil formas.
        // A mí este modo no me gusta, pero valga como ejemplo :D
        string constr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\personal.mdf;Integrated Security=True;User Instance=True";
       
        // Creamos la conexión
        using (SqlConnection con = new SqlConnection(constr))
        {
            // Creamos el Comando
            using (SqlCommand cmd = new SqlCommand("InsertaEjemplo", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(sqls);

                // Esta es la clave, hemos de añadir un parámetro que recogerá el valor de retorno
                SqlParameter retValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                retValue.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(retValue);

                // Abrimos la conexión y ejecutamos el ExecuteReader
                con.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    Id = Convert.ToInt32(retValue.Value);
                }
            }
        }

        return Id;
    }



Ahora ya tenemos el Id, y podemos hacer con él lo que creamos conveniente

Cabe reseñar que junto con el SCOPE_IDENTITY hay otras dos funciones que devuelven información similar: IDENT_CURRENT y @@IDENTITY.

La diferencia entre las tres es básicamente sobre el ámbito y la sesión sobre la que actúan, pero ese ya es otro tema. Ante el escenario que os planteo, el  SCOPE_IDENTITY me parece la mejor opción