Un procedimiento almacenado de SQL Server es un grupo de una o varias instrucciones Transact-SQL. Los procedimientos almacenados pueden:
- aceptar parámetros de entrada y pueden devolver varios valores en forma de parámetros de salida.
- hacer llamadas a otros procedimientos.
- devolver un valor de estado al proceso o programa que realiza la llamada para indicar si la operación se ha realizado correctamente o se han producido errores.
- también pueden devolver el resultado de la ejecución de una consulta.
Las ventajas de usar procedimientos almacenados son un tráfico de red reducido entre el cliente y el servidor, mayor seguridad en el acceso a los objetos de base de datos, fácil mantenimiento y reutilización de código y también mayor rendimiento.
Siempre se ha de poner las instrucciones para el control de errores, esta es la plantilla:
CREATE PROCEDURE PPrueba
@ParamEntrada int,
@ParamSalida int OUTPUT
AS
BEGIN TRY
BEGIN TRAN
-- codigo...
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0 BEGIN
ROLLBACK TRAN
END
DECLARE @CatchError NVARCHAR(MAX)
SET @CatchError=dbo.funImprimeError(ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_PROCEDURE(),@@PROCID ,ERROR_LINE())
RAISERROR(@CatchError,12,1)
-- retorno KO
RETURN 0
END CATCH
-- retorno OK
RETURN -1
Ejemplo: Crear procedimiento para devolver los datos de una factura dada pasado por parámetro. Los datos a devolver serán la fecha, el cliente, y el total de la factura. Usar el depurador de SQL en la ejecución del procedimiento.
CREATE PROCEDURE PPersDameDatosFactura
@IdFactura T_Id_Factura,
@Anyo int OUTPUT,
@ImporteEuros T_Decimal OUTPUT
AS
BEGIN TRY
--BEGIN TRAN
SELECT @Anyo=YEAR(F.FechaFact), @ImporteEuros=SUM(TotalBaseEuros)
FROM Facturas_Cli_Cab F INNER JOIN Facturas_Cli_Totales_Bases B ON F.IdFactura=B.IdFactura
WHERE F.IdFactura=@IdFactura
GROUP BY F.FechaFact
--COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0 BEGIN
ROLLBACK TRAN
END
DECLARE @CatchError NVARCHAR(MAX)
SET @CatchError=dbo.funImprimeError(ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_PROCEDURE(),@@PROCID ,ERROR_LINE())
RAISERROR(@CatchError,12,1)
-- retorno KO
RETURN 0
END CATCH
-- retorno OK
RETURN -1
Así se llama al procedimiento y se recupera los dados:
Declare @Anyo int
Declare @ImporteEuros T_Decimal
Declare @ret int
EXEC @ret=PDameDatosFactura 11, @Anyo OUTPUT, @ImporteEuros OUTPUT
SELECT @ret,@Anyo, @ImporteEuros
Podemos programar procedimientos personalizados mediante los procesos personalizables ya incluidos en la base de datos. Con esta consulta podemos ver todos los procedimientos personalizables existentes en la base de datos:
select name from actualizador_objetos where noactualizar=1 and xtype in ('P') order by 1
En estos procedimientos se dispone llamadas a pre-procesos y post-procesos que inicialmente no realizan acción alguna y en el que podemos insertar nuestro código personalizado. El código del pre-proceso se ejecuta de forma previa al código del proceso que lo llama y el código del post-proceso se ejecuta a posteriori. Ambos procesos pueden seguir con la ejecución del proceso que lo llama o no.
Conoce más sobre transact SQL consultado todo el material que tenemos publicado aquí