Skip to content

Instantly share code, notes, and snippets.

@alfonsomozkoh

alfonsomozkoh/usp_OXIEMP_Imp_PreAnalisis_Actualiza_registro.sql

Last active November 10, 2018 03:33
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alfonsomozkoh/96378e5d4dfdc782ac87ef4f5e934294 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alfonsomozkoh/96378e5d4dfdc782ac87ef4f5e934294 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. alfonsomozkoh revised this gist Nov 10, 2018. 1 changed file with 8 additions and 1 deletion.
    9 changes: 8 additions & 1 deletion usp_OXIEMP_Imp_PreAnalisis_Actualiza_registro.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -26,10 +26,15 @@ ALTER PROCEDURE [dbo].[usp_OXIEMP_Imp_PreAnalisis_Actualiza_registro]
    )
    AS
    BEGIN
    IF @STR_IMP_EXP = 'OXIMP004_FraccionPreAnalisis'
    --yo primero valido que la tabla sea la indicada
    IF @STR_IMP_EXP = 'OXIMP004_FraccionPreAnalisis'
    --inicias una estructura de control try catch para no quemar el atole
    BEGIN TRY
    --validas si existe el registro que vas a actualizar en este caso con un select count
    IF (SELECT COUNT(*) FROM [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis] WHERE [OXIMP004_ID_IN] = @INT_ID) = 1
    --inicias una transacción
    BEGIN TRANSACTION UPDATE_IMP_PA;
    --en mi caso es un update y un insert a la vez en diferentes tablas segun el comportamiento de las variables
    UPDATE OXIMP004_FraccionPreAnalisis
    SET
    [OXIMP004_PaisHomologado_ST] = @PAIS
    @@ -147,9 +152,11 @@ BEGIN
    SET OXIMP004_ProveedorH_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --aqui mandas a concluir la transaccion si y solo si la estructura try dice que esta todo bien
    COMMIT TRANSACTION UPDATE_IMP_PA;
    END TRY
    BEGIN CATCH
    -- si no, el rollback deshace todo para que no se llene la base a lo sope :D
    ROLLBACK TRANSACTION UPDATE_IMP_PA;
    END CATCH
    END
  2. alfonsomozkoh created this gist Oct 25, 2018.
    156 changes: 156 additions & 0 deletions usp_OXIEMP_Imp_PreAnalisis_Actualiza_registro.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,156 @@
    USE [ZOXIMPEXP]
    GO
    /****** Object: StoredProcedure [dbo].[usp_OXIEMP_Imp_PreAnalisis_Actualiza_registro] Script Date: 10/25/2018 10:33:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: Alfonso Mosco H.
    -- Create date: 24-10-2018
    -- Description: Actualiza un registro especifico de la tabla Pre analisis importaciones a demanda de usuario desde el sitio web
    -- =============================================
    ALTER PROCEDURE [dbo].[usp_OXIEMP_Imp_PreAnalisis_Actualiza_registro]
    -- Add the parameters for the stored procedure here
    (
    @STR_IMP_EXP VARCHAR(100),
    @INT_ID INT,
    @PAIS VARCHAR(50),
    @IMPORTADOR_H VARCHAR(100),
    @FAMILIA VARCHAR(100),
    @PRODUCTOS VARCHAR(100),
    @CLASIFICACION VARCHAR(150),
    @DESCRIP_QUIM VARCHAR(100),
    @CONTRATIPO VARCHAR(100),
    @PROVEEDOR_H VARCHAR(100)
    )
    AS
    BEGIN
    IF @STR_IMP_EXP = 'OXIMP004_FraccionPreAnalisis'
    BEGIN TRY
    IF (SELECT COUNT(*) FROM [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis] WHERE [OXIMP004_ID_IN] = @INT_ID) = 1
    BEGIN TRANSACTION UPDATE_IMP_PA;
    UPDATE OXIMP004_FraccionPreAnalisis
    SET
    [OXIMP004_PaisHomologado_ST] = @PAIS
    ,[OXIMP004_ImportadorHomologado_ST] = @IMPORTADOR_H
    ,[OXIMP004_Familia_ST] = @FAMILIA
    ,[OXIMP004_ProductoIdentificados_ST] = @PRODUCTOS
    ,[OXIMP004_Clasificacion_ST] = @CLASIFICACION
    ,[OXIMP004_DescripcionQuimica_ST] = @DESCRIP_QUIM
    ,[OXIMP004_Contratipo_ST] = @CONTRATIPO
    ,[OXIMP004_ProveedorHomologado_ST] = @PROVEEDOR_H
    WHERE [OXIMP004_ID_IN] = @INT_ID
    --ACTUALIZAMOS EL ESTATUS DE LAS BANDERAS
    --STATUS DE PAIS
    IF @PAIS <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET [OXIMP004_PaisH_BT] = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET [OXIMP004_PaisH_BT] = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END

    --IMPORTADOR
    IF @IMPORTADOR_H <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_ImportadorH_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_ImportadorH_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --FAMILIA
    IF @FAMILIA <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_Familia_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_Familia_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --PRODUCTOS IDENTIFICADOS
    IF @PRODUCTOS <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_ProductoIdentificados_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_ProductoIdentificados_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --CLASIFICACION
    IF @CLASIFICACION <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_Clasificacion_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_Clasificacion_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --DESCRIPCION QUIMICA
    IF @DESCRIP_QUIM <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_DescripcionQuimica_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_DescripcionQuimica_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --CONTRATIPO
    IF @CONTRATIPO <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_Contratipo_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_Contratipo_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    --PROVEEDOR
    IF @PROVEEDOR_H <> 'INFORMACIÓN INSUFICIENTE'
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_ProveedorH_BT = 1
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    ELSE
    BEGIN
    UPDATE [ZOXIMPEXP].[dbo].[OXIMP004_FraccionPreAnalisis]
    SET OXIMP004_ProveedorH_BT = 0
    WHERE [OXIMP004_ID_IN] = @INT_ID
    END
    COMMIT TRANSACTION UPDATE_IMP_PA;
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION UPDATE_IMP_PA;
    END CATCH
    END
    GO