sql-server - tabla - procedimiento almacenado sql server select

Procedimiento almacenado: identidad de retorno como parámetro de salida o escalar

Question

Cuando inserta un registro en una tabla con una columna de identidad, puede usar SCOPE_IDENTITY () para obtener ese valor. En el contexto de un procedimiento almacenado, que sería la forma recomendada de devolver el valor de identidad:

1. Como parámetro de salida SET @RETURN_VALUE = SCOPE_IDENTITY()
2. Como escalar, SELECT SCOPE_IDENTITY()
3. ¿De otra manera?

¿Alguna ventaja / desventaja para cada uno?

Answer 1

Otra opción sería como el valor de retorno para el procedimiento almacenado (aunque no lo sugiero, ya que generalmente es mejor para los valores de error).

Lo he incluido como ambos cuando está insertando una sola fila en los casos en que el procedimiento almacenado estaba siendo consumido por otros procedimientos de SQL y un front-end que no podría funcionar con los parámetros OUTPUT (creo que IBATIS en .NET):

CREATE PROCEDURE My_Insert
@col1 VARCHAR(20),
@new_identity INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO My_Table (col1)
VALUES (@col1)
SELECT @new_identity = SCOPE_IDENTITY()
SELECT @new_identity AS id
RETURN
END

El parámetro de salida es más fácil de trabajar en T-SQL cuando llama desde otros procedimientos almacenados de IMO, pero algunos lenguajes de programación tienen poca o ninguna compatibilidad con los parámetros de salida y funcionan mejor con los conjuntos de resultados.

Answer 2

Prefiero devolver el valor de identidad como un parámetro de salida. El resultado del SP debe indicar si tuvo éxito o no. Un valor de 0 indica que el SP se completó con éxito, un valor distinto de cero indica un error. Además, si alguna vez necesita hacer un cambio y devolver un valor adicional desde el SP, no necesita realizar ningún cambio además de agregar un parámetro de salida adicional.

Answer 3

Todo depende de la capa de acceso a los datos de su cliente. Muchos marcos ORM se basan en consultar explícitamente SCOPE_IDENTITY durante la operación de inserción.

Si tiene el control total sobre la capa de acceso a datos, entonces es mejor devolver SCOPE_IDENTITY () como un parámetro de salida. Envolver el retorno en un conjunto de resultados agrega sobrecarga de metadatos innecesarios para describir el conjunto de resultados y complica el código para procesar el resultado de las solicitudes.

Si prefiere una devolución del conjunto de resultados, entonces otra vez es mejor utilizar la cláusula OUTPUT:

INSERT INTO MyTable (col1, col2, col3)
OUTPUT INSERTED.id, col1, col2, col3
VALUES (@col1, @col2, @col3);

De esta forma, puede recuperar toda la fila insertada, incluidas las columnas predeterminadas y calculadas, y obtiene un conjunto de resultados que contiene una fila por cada fila insertada, esto funciona correctamente con las inserciones por lotes orientadas a conjuntos.

En general, no puedo ver un solo caso al devolver SCOPE_IDENTITY() como resultado el conjunto sería una buena práctica.

Answer 4

Ya sea como conjunto de registros o parámetro de salida. Este último tiene menos sobrecarga y yo tendería a usar ese conjunto de registros en lugar de solo una columna / fila.

Si esperaba> 1 fila, usaría la cláusula OUTPUT y un conjunto de registros

Los valores de retorno se usarían normalmente para el manejo de errores.

Answer 5

SELECT IDENT_CURRENT(''databasename.dbo.tablename'') AS your identity column;