[SQL] Como planear un Procedimiento almacenado en SQL Server 2008 (Creación y ejecución).

Saludos amigos, no se si sea realmente el nombre correcto para este post, le idea es que a partir de una sentencia SQL (Select, Insert, Update, Delete, Etc) puedan lograr parametrizar la consulta  y posteriormente elaborar su procedimiento almacenado.

Paso 1. Sentencia SQL a ejecutar.

USE prueba;
GO

INSERT INTO dbo.DEMO_PRODUCTO (nombre, precio, descripcion, fecha)
VALUES('Whisky', 275.50,'12 Años de añejamiento, promoción 2x1',GETDATE());
GO

SELECT TOP 1 * FROM dbo.DEMO_PRODUCTO ORDER BY id DESC;
GO

Nota: deben de asegurarse que la sentencia SQL que ejecuten se realice correctamente.

Paso 2. Parametrizar la Sentencia SQL.

Por cada uno de los campos que se requieren al insertar (para el caso de este ejemplo), deben de convertirse en variables T-SQL con el tipo de dato correspondiente, asignarles el valor del campo a Insertar y sustituirlos en la sentencia SQL.

USE prueba;
GO

DECLARE @producto varchar(100), 
        @precio smallmoney, 
        @descripcion varchar(750);
        
SET @producto = 'Agua mineral';
SET @precio = 12.50;
SET @descripcion = 'Agua mineral de 2 litros';

INSERT INTO dbo.DEMO_PRODUCTO (nombre, precio, descripcion, fecha)
VALUES(@producto, @precio,@descripcion,GETDATE());
GO

SELECT TOP 2 * FROM dbo.DEMO_PRODUCTO ORDER BY id DESC;
GO    

Paso 3. Convertir el Bacth T-SQL a Procedimiento almacenado.

Ya que tenemos la sentencia parametrizada, el paso final es convertirla en procedimiento almacenado, para esto tenemos que agregar la estructura de un “STORE PROCEDURE – SP”  de SQL Server 2008 como lo muestra el siguiente ejemplo:

CREATE PROCEDURE <Procedure_Name, sysname, Procedure_Name>
    <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
    <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
    <Instrucciones T-SQL (Insert, Select, Update, Delete, ETC)>
GO

La primera parte es asignarle un nombre al SP.

La Segunda parte es crear los parámetros que recibirá el SP.

Como tercera parte es crear el Batch de T-SQL que se ejecutara al llamar a nuestro SP.

Ejemplo:

 

El código:

CREATE PROCEDURE InsertaProductoSP
    @producto varchar(100), 
    @precio smallmoney, 
    @descripcion varchar(750)
AS
    INSERT INTO dbo.DEMO_PRODUCTO (nombre, precio, descripcion, fecha)
    VALUES(@producto, @precio,@descripcion,GETDATE());
GO

La ejecución:

USE prueba;
GO

EXEC InsertaProductoSP 'Hielos', 12.90, 'Bolsa de hielos de 3 kilos';
GO

SELECT TOP 3 * FROM dbo.DEMO_PRODUCTO ORDER BY id DESC;
GO

Resultado:

Espero les sea de ayuda, para algunos usuarios el ejemplo les puede parecer sumamente sencillo (y lo es), pero también creo que a los usuarios que inician los ubicara un poco más de como desarrollar en Transact SQL 2008, espero sus comentarios.

Etiquetas de Technorati: Store Procedure,SQL 2008,Crear Procedieminto almacenado,SQL,EXEC SP

[SQL] Procedimiento almacenado para Insertar datos con transacción y control de errores.

Hola amigos, en muchas ocasiones no sabemos si estamos realizando correctamente un proceso de inserción, actualización o eliminación de datos a una tabla(s), esto para muchos es sumamente sencillo, pero me di a la tarea de preparar un pequeño ejemplo de esto.  El script de T-Sql consiste en realizar un procedimiento almacenado que reciba los datos necesarios para insertarlos en la tabla, para garantizar la ejecución correcta de las inserciones utilizo las transacciones “Transact SQL” y para validar la reversión de la transacción en caso de que ocurra un ERROR utilizo el control de Errores Try – Catch con RollBack.

¿Qué es una Transacción?

Una transacción es un conjunto de operaciones T-SQL que funcionan como un solo bloque de instrucciones, esto significa que si ocurre un fallo durante la ejecución de alguna instrucción del bloque todas las demás fallaran, lo que implica que nada más se ejecutara y la transacción tendrá que deshacer todo lo que había ejecutado hasta donde ocurrió el fallo, a eso se la llama reversión de la transacción y se ejecuta con un ROLLBACK, en caso de una ejecución correcta se tendrá que grabar la transacción con COMMIT, el objetivo de las transacciones es garantizar que los datos que se inserten, actualicen o eliminen queden CONSISTENTES en la base de datos.

Después de una ligera explicación de las Transacciones veremos el siguiente ejemplo de una inserción dentro de un procedimiento almacenado con transacción.

Definiendo las estructuras.

1. La estructura del SP es:

CREATE PROCEDURE nombreProdedimiento
    -- Parámetros del SP
    @Param1 AS Varchar(25),
    @Param2 AS Bigint
    .
    .
    .
AS
BEGIN    

    -- Insertar bloque de instrucciones a ejecutar.
    /*
        SELECT
        UPDATE
        INSERT
        DELETE
        Lo que quieras....
    */
END
GO

 

2. La estructura del control de errores TRY-CATCH es:

Begin Try

    /*Bloque de instrucciones a validar.
    -----------------------------------------
    -----------------------------------------
    -----------------------------------------*/

End Try
Begin Catch
    /*Bloque de instrucciones que se ejecutan si ocurre
    un ERROR.
    -----------------------------------------
    -----------------------------------------
    -----------------------------------------*/
End Catch

3. La estructura de una Transacción es:

Begin Tran NombreTransaccion--Inicio de la transacción con su nombre Tadd o el que elijas.

    /*Bloque de instrucciones a ejecutar en la Transacción
    ---------------------------------------
    ---------------------------------------*/

Commit Tran NombreTransaccion--Confirmación de la transacción.

Rollback Tran NombreTransaccion--Reversión de la transacción.

 

4. Para finalizar veremos todo lo anterior armado ya en conjunto de SP, Transacción y control de Errores.

-- =============================================
-- Author:        Ivan Rangel Cuadros.
-- Create date: 22/Ene/2009
-- Description:    Procedimiento para Insertar registros en una Tabla con transacción y control de errores.
-- =============================================
CREATE PROCEDURE spAgregaUsuario
    @nom AS VARCHAR(50),
    @ape AS VARCHAR(50),
    @ema AS VARCHAR(30),
    @pas AS VARCHAR(20),
    @idJer AS BIGINT,
    @msg AS VARCHAR(100) OUTPUT

AS
BEGIN

    SET NOCOUNT ON;

    Begin Tran Tadd

    Begin Try

        INSERT INTO dbo.USUARIO_SYS (nombre, apellidos, email, pass, fecha_add) VALUES (@nom, @ape, @ema, @pas, GETDATE())

        INSERT INTO dbo.USUARIO_JERARQUIAS_SYS (id_usuario, id_jerarquia) VALUES (@@IDENTITY, @idJer)

        SET @msg = 'El Usuario se registro correctamente.'

        COMMIT TRAN Tadd

    End try
    Begin Catch

        SET @msg = 'Ocurrio un Error: ' + ERROR_MESSAGE() + ' en la línea ' + CONVERT(NVARCHAR(255), ERROR_LINE() ) + '.'
        Rollback TRAN Tadd

    End Catch

END
GO

 

Explicando el procedimiento:

1. Recibe los datos a través de parámetros de los cuales uno es de salida OutPut , que servirá para indicar si la Inserción se realizo correctamente o no.

2. Crea una transacción llamada Tadd, valida con TRY-CATCH las inserciones a las tablas USUARIO y USUARIO_JERARQUIAS.

3. Si las operaciones de inserción se ejecutan correctamente las confirma con COMMIT TRAN Tadd y regresa la variable @msg con un valor de confirmación correcto, si ocurre un error se revierte la transacción con ROLLBACK TRAN Tadd y devuelve en @msg un valor de error.

Ejecutando el Procedimiento:

DECLARE @msg AS VARCHAR(100);
EXEC spAgregaUsuario 'Claudia','Perez Torres','clau@mail.com','a220109',1,@msg OUTPUT
SELECT @msg AS msg

Mensaje de inserción correcta:

Mensaje de Error en la Transacción:

 

Apóyame votando aquí: http://blogit.ms/TopBloggers.aspx en la categoría SQL Server.

Los pasos para votar están aquí te llevara 3 min: Clic Aquí. 

Espero les sea de ayuda. Dejen comentarios!!!

Etiquetas de Technorati: SQL,SQL 2005,Procedimeintos almacenados,Trasacciones,T-SQL,TSQL,Control de errores,Try-Catch,Transact SQL,Store Procedure