Objetos de bases de datos. Procedimientos almacenados

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í

<< Artículo anterior                                                                                                                                       Siguiente artículo >>