Stored Procedure with Input and Output Parameters. Return Values. Usar un procedimiento almacenado con parámetros de entrada y salida. SQL Server.

 Un procedimiento almacenado de SQL Server al que se puede llamar es el que devuelve uno o más parámetros OUT, que son los parámetros que el procedimiento almacenado usa para devolver los datos a la aplicación que realiza la llamada

Voy a comentar como utilizar esas variables que transportan información de salida o entrada en los procedimientos, para poder trabajar y pasar dichas variables de un procedimiento a otro. Por ser una duda comun en personas que se inician en SQL Server.

 En el ejemplo que os presento, añado como obtener, el último auto-numérico asignado de la tabla de pedidos, desde un procedimiento de alta de pedidos (InsertarPedido), para pasárselo al procedimiento de alta de detalle de pedidos (InsertarDetalle).

 Para el que no lo sepa, un campo numérico puede tener un atributo extra "identity". Los valores de un campo con este atributo genera valores secuenciales que se inician en 1 y se incrementan en 1 automáticamente (auto-numérico), para generar valores únicos para cada nuevo registro que se inserta en una misma tabla.

 Lo único que debemos hacer es añadir un parámetro al procedimiento de alta de pedidos, de tipo entrada y salida (poniendo "OUTPUT"). Ese parámetro lo cargas mediante SCOPE_IDENTITY(), una función que te captura el último identity insertado. Sería más o menos así:

create procedure InsertarPedido

@p1 int, @p2 varchar(10), ..., @IdPedido int OUTPUT as

 

/*Aquí metes todo el código*/

 

--Insertar el pedido

insert Pedidos values (@p1, @p2)

 

--Justo después, capturas el autonumérico

select @IdPedido = SCOPE_IDENTITY()-- también puedes capturar error y otras necesidades que tengas…

 

return 0

 

 

 Nota.- Como veis capturo el identificador recién insertado para llevarlo a la segunda tabla. Uso la función SCOPE_IDENTITY(). Tratar de obtenerlo con un select max tiene dos problemas. Uno, funcional: se puede producir otra inserción desde que lo insertas hasta que lo consultas y obtendrías el Id que no es. Otro, de rendimiento: ese "select max" chocará con la siguiente inserción y sufrirás bloqueos.

 

 Luego, cuando llamemos al procedimiento de alta de pedidos, hemos de capturar ese valor en una variable que luego le pasaremos como parámetro de entrada al procedimiento de inserción de los detalles:

declare @VariablePedido int

 

exec InsertarPedido @p1 = 5, @p2 = 'MiPedido', @IdPedido = @VariablePedido OUTPUT

 

select NuevoPedido = @VariablePedido

--Y esto mismo lo pasas a la inserción del detalle como parámetro de entrada

 

exec InsertarDetalle @d1 = 5, @d2 = 'MiDetalle', @pIdPedido = @VariablePedido

 

 Dejo un enlace a un articulo, de tema relacionado, que será de vuestro interes : Stored Procedure with array Parameters. Como pasamos variables tipo tabla (arrays) a procedimientos almacenados. SQL Server.

 

 

Fuentes:

Microsoft, msdn.

 

Apunte y recopilación por Norman M. Pardell

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.