Etiquetas

SQL Server (Buenas practicas)

 


El uso de procedimientos almacenados en lugar de vistas o consultas directas en una base de datos SQL Server tiene varias ventajas, dependiendo del caso de uso y las necesidades del sistema. Aquí están las principales razones:

1. Rendimiento optimizado

  • Planes de ejecución precompilados: Los procedimientos almacenados son precompilados y almacenados en la memoria caché del servidor, lo que reduce el tiempo de ejecución al evitar que el motor de la base de datos vuelva a compilar la consulta cada vez que se ejecuta.
  • Optimización específica: Puedes ajustar un procedimiento almacenado para optimizar el rendimiento de operaciones específicas, algo que no es posible con vistas o consultas ad hoc.

2. Reutilización de código

  • Los procedimientos almacenados permiten encapsular la lógica de negocio y reutilizarla en diferentes partes de una aplicación sin necesidad de replicar la lógica en varias consultas.

3. Mantenimiento y escalabilidad

  • Centralización de la lógica: Si necesitas realizar cambios en la lógica de negocio, puedes hacerlo en un solo lugar (el procedimiento almacenado) en lugar de actualizar múltiples consultas en el código de la aplicación o en otras vistas.
  • Compatibilidad a largo plazo: Si la estructura de la base de datos cambia, puedes modificar el procedimiento almacenado sin impactar directamente al código de la aplicación.

4. Seguridad

  • Control de acceso: Puedes otorgar permisos a usuarios para ejecutar un procedimiento almacenado sin darles acceso directo a las tablas subyacentes.
  • Prevención de inyecciones SQL: Los procedimientos almacenados pueden minimizar el riesgo de inyecciones SQL si se diseñan correctamente utilizando parámetros.

5. Flexibilidad

  • Condicionales y bucles: A diferencia de las vistas, los procedimientos almacenados pueden incluir lógica condicional y ciclos, lo que permite realizar operaciones más complejas.
  • Manipulación de datos: Un procedimiento almacenado puede incluir múltiples operaciones, como la actualización, inserción o eliminación de datos, en una sola ejecución.

6. Eficiencia en operaciones repetitivas

  • Si tienes consultas o procesos que se ejecutan frecuentemente con parámetros diferentes, un procedimiento almacenado permite manejar estas variaciones de manera eficiente al pasar los parámetros en tiempo de ejecución.

7. Facilidad de integración

  • Los procedimientos almacenados se pueden integrar fácilmente con herramientas de automatización, aplicaciones externas o sistemas de gestión empresarial (ERP, CRM, etc.) mediante su ejecución directa.

Cuándo usar vistas o consultas directas en su lugar

  • Vistas: Son útiles para simplificar el acceso a datos predefinidos o para encapsular consultas complejas reutilizables sin incluir lógica procedimental.
  • Consultas directas: Pueden ser útiles para operaciones rápidas y simples, especialmente durante el desarrollo o pruebas, pero no son ideales para un entorno de producción.

En resumen, los procedimientos almacenados son más adecuados para escenarios donde se requiere lógica compleja, control de acceso, eficiencia, o mantenimiento a largo plazo. Sin embargo, la elección entre procedimientos, vistas y consultas directas debe basarse en los requisitos específicos del sistema y las necesidades del negocio.

Esquemas

En SQL Server, un esquema es una estructura lógica que organiza y agrupa objetos de la base de datos, como tablas, vistas, procedimientos almacenados y funciones. Los esquemas ayudan a gestionar la seguridad, organización y mantenimiento de las bases de datos, especialmente en entornos grandes o multiusuario.

¿Qué es un esquema?

Un esquema es un contenedor dentro de una base de datos que agrupa objetos relacionados. Cada objeto en la base de datos pertenece a un esquema y está identificado por un nombre calificado de la forma:

esquema.objeto

Por ejemplo:

ventas.productos

En este caso, ventas es el esquema y productos es la tabla.

Razones para usar esquemas

  1. Organización de objetos:

    • Agrupar objetos relacionados para mejorar la legibilidad y el mantenimiento.
    • Ejemplo: Agrupar tablas relacionadas con el departamento de ventas en un esquema ventas.

  2. Control de seguridad:

    • Los permisos pueden asignarse a nivel de esquema, lo que facilita la gestión de acceso.
    • Ejemplo: Conceder acceso completo al esquema finanzas solo a usuarios del departamento financiero.

  3. Separación lógica:

    • En bases de datos compartidas por varios equipos o aplicaciones, los esquemas permiten separar lógicamente los objetos de cada grupo.
    • Ejemplo: Un esquema app1 para una aplicación y otro app2 para otra.

  4. Prevención de conflictos de nombres:

    • Permite crear objetos con el mismo nombre en diferentes esquemas sin conflicto.
    • Ejemplo: ventas.clientes y soporte.clientes.

  5. Compatibilidad con mejores prácticas:

    • Usar esquemas en lugar de la propiedad predeterminada (dbo) permite un diseño más profesional y escalable.

Comandos básicos para trabajar con esquemas

1. Crear un esquema

Para crear un nuevo esquema, utiliza el comando CREATE SCHEMA.

Ejemplo:

CREATE SCHEMA ventas;

También puedes asignar un propietario al esquema:

CREATE SCHEMA finanzas AUTHORIZATION usuario_finanzas;

2. Crear objetos dentro de un esquema

Especifica el esquema al crear objetos:

  • Crear una tabla en un esquema:

    CREATE TABLE ventas.productos (
        id INT PRIMARY KEY,
    nombre VARCHAR(50),
    precio DECIMAL(10, 2)
);

  • Crear un procedimiento almacenado en un esquema:

    CREATE PROCEDURE ventas.obtenerProductos
    AS
BEGIN
    SELECT * FROM ventas.productos;
END;

3. Consultar objetos de un esquema

Para acceder a un objeto, utiliza su nombre calificado:

SELECT * FROM ventas.productos;

Si omites el esquema, SQL Server buscará en el esquema predeterminado (dbo).

4. Cambiar el esquema de un objeto

Puedes mover un objeto de un esquema a otro utilizando ALTER SCHEMA:

ALTER SCHEMA finanzas TRANSFER ventas.productos;

Esto moverá la tabla productos del esquema ventas al esquema finanzas.

5. Eliminar un esquema

Para eliminar un esquema, primero debes eliminar o transferir todos sus objetos:

DROP SCHEMA ventas;

Gestión de permisos en esquemas

Los esquemas simplifican la administración de permisos porque puedes asignar acceso a todos los objetos dentro de un esquema en lugar de hacerlo objeto por objeto.

  • Conceder permisos a nivel de esquema:

    GRANT SELECT, INSERT ON SCHEMA::ventas TO usuario_ventas;

  • Revocar permisos:

    REVOKE SELECT ON SCHEMA::ventas FROM usuario_ventas;

Comenzando a trabajar

Cresamos la base de datos 'Municipios'.

Para crear una base de datos llamada Municipios en SQL Server y asegurarte de que no exista previamente, puedes utilizar el siguiente script SQL:

-- Verificamos la existencia de una base con el mismo nombre
IF NOT EXISTS (
    SELECT 1
    FROM sys.databases
    WHERE name = 'Municipios'
)
BEGIN
    --Creamos la base de datos
    CREATE DATABASE Municipios;
    -- Mandamos el mensaje de exito
    PRINT 'Base de datos "Municipios" creada exitosamente.';
END
ELSE
BEGIN
    PRINT 'La base de datos "Municipios" ya existe.';
END;

Desglose del código:

  1. sys.databases: Vista del sistema que contiene información sobre todas las bases de datos en la instancia de SQL Server.
  2. IF NOT EXISTS: Comprueba si no existe una base de datos con el nombre especificado.
  3. CREATE DATABASE Municipios: Crea la base de datos si no existe.
  4. PRINT: Proporciona un mensaje en función de si la base de datos se creó o ya existía.

En SQL Server, los bloques BEGIN ... END se utilizan para agrupar múltiples instrucciones que deben ejecutarse como un conjunto dentro de una estructura de control (como un IF, WHILE, TRY ... CATCH, etc.).

Si no usas BEGIN ... END, solo la primera instrucción después de una estructura de control será parte de ella.

¿Por qué usar BEGIN ... END?

  1. Agrupar múltiples instrucciones: Permite ejecutar varias líneas de código dentro de un contexto controlado por una estructura como IF, WHILE, etc.
  2. Legibilidad y claridad: Mejora la organización del código y facilita su lectura.
  3. Evitar errores lógicos: Sin BEGIN ... END, solo se ejecutará una línea en estructuras como IF, lo que puede causar errores si se necesita ejecutar más de una.

Creamos el esquema llamado territorio en la base de datos Municipios y asegurar de que no exista previamente.


USE Municipios; -- Cambiar al contexto de la base de datos Municipios

IF NOT EXISTS (
    SELECT 1
    FROM sys.schemas
    WHERE name = 'territorio'
)
BEGIN
    CREATE SCHEMA territorio;
    PRINT 'Esquema "territorio" creado exitosamente.';
END
ELSE
BEGIN
    PRINT 'El esquema "territorio" ya existe.';
END;

Explicación del script

  1. USE Municipios: Cambia el contexto a la base de datos Municipios para asegurarte de que el esquema se crea en la base de datos correcta.

  2. sys.schemas: Vista del sistema que contiene información sobre todos los esquemas en la base de datos actual.

    • La columna name se usa para buscar el nombre del esquema.

  3. IF NOT EXISTS: Comprueba si el esquema con el nombre territorio no existe en la base de datos actual.

  4. CREATE SCHEMA territorio: Crea el esquema llamado territorio.

  5. Mensajes de salida:

    • Si el esquema no existe, lo crea y muestra un mensaje indicando que se creó.
    • Si ya existe, muestra un mensaje indicando que ya está presente.

Notas

  • Esquema en SQL Server: Es un contenedor lógico dentro de una base de datos para organizar objetos como tablas, vistas y procedimientos almacenados.
  • Asegúrate de tener los permisos adecuados para crear esquemas en la base de datos.
  • Si la base de datos Municipios no existe, este script generará un error. Asegúrate de ejecutarlo después de crear la base de datos.
Vamos a crear una tabla logs, también conocidos como archivos de registro, son registros de eventos que ocurren en un sistema informático. Son útiles para supervisar y diagnosticar problemas, así como para garantizar la seguridad

USE Municipios;
GO

-- Verificar si la tabla "territorio.logs" existe
IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'territorio'
      AND TABLE_NAME = 'logs'
)
BEGIN
    -- Crear la tabla "territorio.logs"
    CREATE TABLE territorio.logs (
        IdLog INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        NameSP NVARCHAR(MAX) NULL, -- Nombre del procedimiento que se ejecuta
        Parametros NVARCHAR(MAX) NULL, -- Información adicional como usuario o parámetros
        FechaInicioEjecucion DATETIME NOT NULL, 
        FechaFinEjecucion DATETIME NULL,
        TiempoEjecucion INT NULL, -- Tiempo de ejecución en milisegundos
        CONSTRAINT PK_logs PRIMARY KEY (IdLog) -- Llave primaria
    );
    PRINT 'Tabla "territorio.logs" creada exitosamente.';
END
ELSE
BEGIN
    PRINT 'La tabla "territorio.logs" ya existe.';
END;
GO
Crear la tabla municipios con los campos requeridos y verificar si ya existe en la base de datos Municipios. La tabla también incluye un campo geometry para manejar geometrías (usando el tipo de datos geometry de SQL Server).
USE Municipios; -- Cambiar al contexto de la base de datos Municipios
IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'territorio'
      AND TABLE_NAME = 'municipios'
)
BEGIN
    -- Crear la tabla "territorio.municipios"
    CREATE TABLE territorio.municipios (
        nombre NVARCHAR(100) NOT NULL, -- Nombre del municipio
        clave_municipio INT NOT NULL, -- Clave única del municipio
        entidad NVARCHAR(100) NOT NULL, -- Nombre de la entidad federativa
        clave_entidad INT NOT NULL, -- Clave única de la entidad federativa
        geometry GEOMETRY NULL, -- Campo para datos espaciales
        active INT DEFAULT 1, -- Indicador de estado activo/inactivo
        user_capture_id INT NOT NULL, -- ID del usuario que capturó el registro
        user_update_id INT NULL, -- ID del usuario que actualizó el registro
        user_delete_id INT NULL, -- ID del usuario que eliminó el registro
        created_at DATETIME DEFAULT GETDATE(), -- Fecha de creación
        capture_at DATETIME NULL, -- Fecha de captura
        updated_at DATETIME NULL, -- Fecha de actualización
        deleted_at DATETIME NULL, -- Fecha de eliminación
        CONSTRAINT PK_municipios PRIMARY KEY (clave_municipio, clave_entidad) -- Llave primaria compuesta
    );
    PRINT 'Tabla "territorio.municipios" creada exitosamente.';
END
ELSE
BEGIN
    PRINT 'La tabla "territorio.municipios" ya existe.';
END;
GO

Explicación del script

  1. Verificar la existencia de la tabla:

    • Utilizamos la vista INFORMATION_SCHEMA.TABLES para verificar si la tabla municipios existe en el esquema territorio.
    • TABLE_SCHEMA y TABLE_NAME se usan para filtrar por esquema y nombre de la tabla.

  2. Crear la tabla:

    • nombre: Almacena el nombre del municipio como texto (NVARCHAR(100)).
    • clave_municipio: Clave única para identificar el municipio (INT).
    • entidad: Nombre de la entidad federativa (NVARCHAR(100)).
    • clave_entidad: Clave única para identificar la entidad (INT).
    • geometry: Campo de tipo GEOMETRY para almacenar geometrías espaciales (como puntos, polígonos, etc.).
    • Llave primaria (PRIMARY KEY): Combina las claves clave_municipio y clave_entidad para garantizar la unicidad.

  3. Mensajes de salida:

    • Si la tabla no existe, se crea y muestra un mensaje indicando que se creó.
    • Si ya existe, muestra un mensaje indicando que no se creó porque ya está presente.

  4. Consideraciones sobre el campo geometry:

    • Asegúrate de que la base de datos Municipios tenga habilitada la compatibilidad espacial.
    • Puedes ejecutar el siguiente comando para verificar si el tipo GEOMETRY está disponible:
      SELECT name FROM sys.types WHERE name = 'geometry';
    • Si aparece un resultado, el tipo GEOMETRY ya está habilitado.
    • Si no aparece, asegúrate de que SQL Server esté configurado correctamente.

    • Las funcionalidades espaciales están disponibles en las ediciones de SQL Server que soportan características espaciales, como:

      • SQL Server Standard Edition
      • SQL Server Enterprise Edition
      • SQL Server Developer Edition

      Si estás usando una edición que no soporta estas características (como SQL Server Express), necesitarás cambiar a una edición compatible.

  • Ejecuta este script:

    • Asegúrate de haber creado la base de datos Municipios y el esquema territorio antes de ejecutar este script.
    Para insertar los datos en la tabla recien creada vamos a empezar a validar el proceso de insertar datos y el manejo de posibles errores. para esto se utilizara lo siguiente.

    Proceso Almacenado

    USE Municipios;
GO

-- Verificar existencia del procedimiento almacenado
IF EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[territorio].[InsertarMunicipios]')
    AND type = N'P'
)
BEGIN
    PRINT 'El procedimiento "InsertarMunicipios" ya existe. Modificando...';
END
ELSE
BEGIN
    PRINT 'El procedimiento "InsertarMunicipios" no existe. Creando...';

    -- Crear el procedimiento almacenado
    CREATE PROCEDURE territorio.InsertarMunicipios
    AS
    BEGIN
        SET NOCOUNT ON;
        PRINT 'Procedimiento en desarrollo.';
        BEGIN TRY
            BEGIN TRANSACTION;

            -- Verificar si el esquema "territorio" existe
            IF NOT EXISTS (
                SELECT 1
                FROM sys.schemas
                WHERE name = 'territorio'
            )
            BEGIN
                PRINT 'El esquema "territorio" no existe. Creando esquema...';
                CREATE SCHEMA territorio;
            END

            -- Verificar si la tabla "municipios" existe en el esquema "territorio"
            IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = 'territorio'
                AND TABLE_NAME = 'municipios'
            )
            BEGIN
                PRINT 'La tabla "territorio.municipios" no existe. Creando tabla...';
                CREATE TABLE territorio.municipios (
                    nombre NVARCHAR(100) NOT NULL, -- Nombre del municipio
                    clave_municipio INT NOT NULL, -- Clave única del municipio
                    entidad NVARCHAR(100) NOT NULL, -- Nombre de la entidad federativa
                    clave_entidad INT NOT NULL, -- Clave única de la entidad federativa
                    geometry GEOMETRY NULL, -- Campo para datos espaciales
                    active INT DEFAULT 1, -- Indicador de estado activo/inactivo
                    user_capture_id INT NOT NULL, -- ID del usuario que capturó el registro
                    user_update_id INT NULL, -- ID del usuario que actualizó el registro
                    user_delete_id INT NULL, -- ID del usuario que eliminó el registro
                    created_at DATETIME DEFAULT GETDATE(), -- Fecha de creación
                    capture_at DATETIME NULL, -- Fecha de captura
                    updated_at DATETIME NULL, -- Fecha de actualización
                    deleted_at DATETIME NULL, -- Fecha de eliminación
                    CONSTRAINT PK_municipios PRIMARY KEY (clave_municipio, clave_entidad) -- Llave primaria compuesta
                );
            END

            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            PRINT 'Error al crear el procedimiento:';
            PRINT ERROR_MESSAGE();
            THROW;
        END CATCH
    END
END;
GO

-- Modificar el procedimiento almacenado
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE territorio.InsertarMunicipios
    @IdUsuario INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NameSP NVARCHAR(50) = 'InsertarMunicipios',
            @Parametros NVARCHAR(MAX),
            @ENTITYID INT,
            @FechaInicioEjecucion DATETIME = GETDATE();

    -- Validar el parámetro @IdUsuario
    IF @IdUsuario IS NULL OR @IdUsuario <= 0
    BEGIN
        THROW 50000, 'El parámetro @IdUsuario no es válido. Debe ser mayor a 0.', 1;
    END

    SET @Parametros = '@Usuario:' + CONVERT(NVARCHAR(10), @IdUsuario);

    BEGIN TRY
        -- Insertar en log y obtener ENTITYID
        INSERT INTO territorio.logs (NameSP, Parametros, IdUsuario, FechaInicioEjecucion) 
        VALUES (@NameSP, @Parametros, @IdUsuario, @FechaInicioEjecucion); 
        SET @ENTITYID = SCOPE_IDENTITY();

        BEGIN TRANSACTION;

        -- Insertar los datos en la tabla
        PRINT 'Insertando datos en la tabla "territorio.municipios"...';
        INSERT INTO territorio.municipios (
            nombre, 
            clave_municipio, 
            entidad, 
            clave_entidad, 
            geometry,
            active,
            user_capture_id,
            user_update_id,
            user_delete_id,
            created_at,
            capture_at,
            updated_at,
            deleted_at
        )
        VALUES 
        ('Municipio A', 101, 'Entidad A', 1, geometry::STGeomFromText('POINT(100.5 20.5)', 4326), 1, @IdUsuario, NULL, NULL, GETDATE(), GETDATE(), NULL, NULL),
        ('Municipio B', 102, 'Entidad A', 1, geometry::STGeomFromText('POINT(101.0 21.0)', 4326), 1, @IdUsuario, NULL, NULL, GETDATE(), GETDATE(), NULL, NULL),
        ('Municipio C', 201, 'Entidad B', 2, geometry::STGeomFromText('POINT(102.5 22.5)', 4326), 1, @IdUsuario, NULL, NULL, GETDATE(), GETDATE(), NULL, NULL);

        PRINT 'Datos insertados correctamente.';

        -- Actualizar log
        UPDATE territorio.logs
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @FechaInicioEjecucion, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', @ENTITYID)
        WHERE IdLog = @ENTITYID;

        SELECT 1 AS success, 'Se guardó con éxito' AS message;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Error al ejecutar el procedimiento:';
        PRINT ERROR_MESSAGE();

        -- Actualizar el log con el error
        UPDATE territorio.logs
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @FechaInicioEjecucion, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', ISNULL(@ENTITYID, ''))
        WHERE IdLog = @ENTITYID;

        THROW;
    END CATCH
END;
GO

-- Ejecución de pruebas
-- EXEC territorio.InsertarMunicipios @IdUsuario = 1;

    Explicación del Proceso Almacenado

    1. Validación del esquema territorio:

      • Se verifica si el esquema existe utilizando sys.schemas.
      • Si no existe, se crea automáticamente con CREATE SCHEMA territorio.

    2. Validación de la tabla municipios:

      • Se verifica si la tabla existe en el esquema territorio utilizando INFORMATION_SCHEMA.TABLES.
      • Si no existe, se crea automáticamente con los campos especificados, incluyendo la clave primaria combinada (clave_municipio, clave_entidad).

    3. Inserción de datos:

      • Los datos especificados en la consulta original se insertan en la tabla.

    4. Manejo de errores:

      • En caso de cualquier error, el bloque CATCH captura y muestra el mensaje de error con ERROR_MESSAGE().

    5. Mensajes de salida:

      • Se incluyen mensajes PRINT para informar sobre el progreso y los pasos realizados.

    Ejecutar el Proceso Almacenado

    Una vez creado el procedimiento almacenado, puedes ejecutarlo con el siguiente comando:


    EXEC InsertarMunicipios;

    Notas

    • Ejecuciones repetidas: El procedimiento se puede ejecutar varias veces sin problemas, ya que verifica previamente la existencia del esquema y la tabla.
    • Ampliación del procedimiento: Puedes adaptar el procedimiento para aceptar parámetros de entrada y personalizar los datos a insertar dinámicamente.

    Consultamos nuestros datos ingresados en la tabla con el siguiente proceso.

    USE Municipios;
GO

-- Verificar si el procedimiento almacenado existe y modificarlo si es necesario
IF EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'territorio.SeleccionarMunicipios')
    AND type = N'P'
)
BEGIN
    PRINT 'El procedimiento almacenado "territorio.SeleccionarMunicipios" existe.';
END
ELSE
BEGIN
    EXEC(
    'CREATE PROCEDURE territorio.SeleccionarMunicipios (
        @IdUsuario INT,
        @elem_pag INT,
        @pagina INT,
        @clv_ent INT
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        PRINT ''Procedimiento en desarrollo.''; 
    END');
END;
GO

-- Modificar o crear el procedimiento almacenado
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE territorio.SeleccionarMunicipios (
    @IdUsuario INT, 
    @elem_pag INT, 
    @pagina INT, 
    @clv_ent INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validación de parámetros
    IF @IdUsuario IS NULL OR @IdUsuario <= 0
        THROW 50000, 'El parámetro @IdUsuario no es válido. Debe ser mayor a 0.', 1;
    IF @elem_pag IS NULL OR @elem_pag <= 0
        THROW 50000, 'El parámetro @elem_pag no es válido. Debe ser mayor a 0.', 1;
    IF @pagina IS NULL OR @pagina <= 0
        THROW 50000, 'El parámetro @pagina no es válido. Debe ser mayor a 0.', 1;
    IF @clv_ent IS NULL OR @clv_ent <= 0
        THROW 50000, 'El parámetro @clv_ent no es válido. Debe ser mayor a 0.', 1;

    DECLARE 
        @NameSP NVARCHAR(50) = 'SeleccionarMunicipios', 
        @Parametros NVARCHAR(MAX) = CONCAT('@IdUsuario:', @IdUsuario, ', @clv_ent:', @clv_ent), 
        @ENTITYID BIGINT,
        @fchInicioEje SMALLDATETIME = GETDATE();

    BEGIN TRY
        -- Registrar inicio en log
        INSERT INTO log.logUAJT (NameSP, Parametros, IdUsuario, FechaInicioEjecucion) 
        VALUES (@NameSP, @Parametros, @IdUsuario, @fchInicioEje);

        SET @ENTITYID = SCOPE_IDENTITY();

        BEGIN TRANSACTION;

        -- Crear tabla temporal
        IF OBJECT_ID('tempdb..#tmp_datos') IS NOT NULL DROP TABLE #tmp_datos;

        CREATE TABLE #tmp_datos (
            nombre NVARCHAR(100),
            clave_municipio INT,
            entidad NVARCHAR(100),
            clave_entidad INT,
            geometry GEOMETRY
        );

        INSERT INTO #tmp_datos
        SELECT 
            nombre,
            clave_municipio,
            entidad,
            clave_entidad,
            geometry
        FROM territorio.municipios
        WHERE active = 1;

        -- Paginación
        WITH datos_paginados AS (
            SELECT
                ROW_NUMBER() OVER (ORDER BY clave_municipio) AS rownum,
                geometry.STAsText() AS Geom,
                nombre AS municipio,
                COUNT(*) OVER () AS total_records
            FROM #tmp_datos
            WHERE clave_entidad = @clv_ent
        )
        SELECT 
            rownum,
            Geom,
            municipio,
            total_records
        FROM datos_paginados
        WHERE rownum BETWEEN (@pagina - 1) * @elem_pag + 1 AND @pagina * @elem_pag;

        -- Actualizar log
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @fchInicioEje, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', @ENTITYID)
        WHERE IdLog = @ENTITYID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- Registrar error en log
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @fchInicioEje, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', ISNULL(@ENTITYID, ''))
        WHERE IdLog = @ENTITYID;

        THROW;
    END CATCH;

    -- Limpiar tabla temporal
    IF OBJECT_ID('tempdb..#tmp_datos') IS NOT NULL DROP TABLE #tmp_datos;
END;
GO

-- Ejecución de pruebas
-- EXEC territorio.SeleccionarMunicipios @IdUsuario = 1, @elem_pag = 10, @pagina = 1, @clv_ent = 16;

    Continuando con las consulta a la tabla ... nos toca ver el procedimiento de actualizacion de un registro 

    USE Municipios;
GO

-- Verificar si el procedimiento almacenado existe y modificarlo si es necesario
IF EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'territorio.ActualizarMunicipios')
    AND type = N'P'
)
BEGIN
    PRINT 'El procedimiento almacenado "territorio.ActualizarMunicipios" existe.';
END
ELSE
BEGIN
    EXEC(
    'CREATE PROCEDURE territorio.ActualizarMunicipios (
        @IdUsuario INT,
        @clave_municipio INT,
        @nombre NVARCHAR(100),
        @clave_entidad INT
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        PRINT ''Procedimiento en desarrollo.'';
    END');
END;
GO

-- Modificar o crear el procedimiento almacenado
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE territorio.ActualizarMunicipios (
    @IdUsuario INT,
    @clave_municipio INT,
    @nombre NVARCHAR(100),
    @clave_entidad INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validación de parámetros
    IF @IdUsuario IS NULL OR @IdUsuario <= 0
        THROW 50000, 'El parámetro @IdUsuario no es válido. Debe ser mayor a 0.', 1;
    IF @clave_municipio IS NULL OR @clave_municipio <= 0
        THROW 50000, 'El parámetro @clave_municipio no es válido. Debe ser mayor a 0.', 1;
    IF @nombre IS NULL OR LEN(@nombre) = 0
        THROW 50000, 'El parámetro @nombre no puede estar vacío.', 1;
    IF @clave_entidad IS NULL OR @clave_entidad <= 0
        THROW 50000, 'El parámetro @clave_entidad no es válido. Debe ser mayor a 0.', 1;

    DECLARE 
        @NameSP NVARCHAR(50) = 'ActualizarMunicipios',
        @Parametros NVARCHAR(MAX) = CONCAT(
            '@IdUsuario:', @IdUsuario, 
            ', @clave_municipio:', @clave_municipio, 
            ', @nombre:', @nombre,
            ', @clave_entidad:', @clave_entidad
        ),
        @ENTITYID BIGINT,
        @fchInicioEje SMALLDATETIME = GETDATE();

    BEGIN TRY
        -- Registrar inicio en log
        INSERT INTO log.logUAJT (NameSP, Parametros, IdUsuario, FechaInicioEjecucion) 
        VALUES (@NameSP, @Parametros, @IdUsuario, @fchInicioEje);

        SET @ENTITYID = SCOPE_IDENTITY();

        BEGIN TRANSACTION;

        -- Actualizar registro en la tabla municipios
        UPDATE territorio.municipios
        SET nombre = @nombre,
            clave_entidad = @clave_entidad,
            FechaActualizacion = GETDATE()
        WHERE clave_municipio = @clave_municipio;

        -- Validar si se actualizó el registro
        IF @@ROWCOUNT = 0
            THROW 50000, 'No se encontró un registro con la clave_municipio especificada.', 1;

        -- Actualizar log
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @fchInicioEje, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', @ENTITYID)
        WHERE IdLog = @ENTITYID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- Registrar error en log
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @fchInicioEje, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', ISNULL(@ENTITYID, '')),
            ErrorMensaje = ERROR_MESSAGE()
        WHERE IdLog = @ENTITYID;

        THROW;
    END CATCH;
END;
GO

-- Ejecución de prueba
-- EXEC territorio.ActualizarMunicipios @IdUsuario = 1, @clave_municipio = 101, @nombre = 'Nuevo Nombre', @clave_entidad = 16;

    y por ultimo veremos la eliminacion de un registro

    USE Municipios;
GO

-- Verificar si el procedimiento almacenado existe y modificarlo si es necesario
IF EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'territorio.EliminarMunicipio')
    AND type = N'P'
)
BEGIN
    PRINT 'El procedimiento almacenado "territorio.EliminarMunicipio" existe.';
END
ELSE
BEGIN
    EXEC(
    'CREATE PROCEDURE territorio.EliminarMunicipio (
        @IdUsuario INT,
        @clave_municipio INT
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        PRINT ''Procedimiento en desarrollo.'';
    END');
END;
GO

-- Modificar o crear el procedimiento almacenado
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE territorio.EliminarMunicipio (
    @IdUsuario INT,
    @clave_municipio INT
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validación de parámetros
    IF @IdUsuario IS NULL OR @IdUsuario <= 0
        THROW 50000, 'El parámetro @IdUsuario no es válido. Debe ser mayor a 0.', 1;
    IF @clave_municipio IS NULL OR @clave_municipio <= 0
        THROW 50000, 'El parámetro @clave_municipio no es válido. Debe ser mayor a 0.', 1;

    DECLARE 
        @NameSP NVARCHAR(50) = 'EliminarMunicipio',
        @Parametros NVARCHAR(MAX) = CONCAT(
            '@IdUsuario:', @IdUsuario, 
            ', @clave_municipio:', @clave_municipio
        ),
        @ENTITYID BIGINT,
        @fchInicioEje SMALLDATETIME = GETDATE();

    BEGIN TRY
        -- Registrar inicio en log
        INSERT INTO log.logUAJT (NameSP, Parametros, IdUsuario, FechaInicioEjecucion) 
        VALUES (@NameSP, @Parametros, @IdUsuario, @fchInicioEje);

        SET @ENTITYID = SCOPE_IDENTITY();

        BEGIN TRANSACTION;

        -- Eliminar registro en la tabla municipios
        DELETE FROM territorio.municipios
        WHERE clave_municipio = @clave_municipio;

        -- Validar si se eliminó algún registro
        IF @@ROWCOUNT = 0
            THROW 50000, 'No se encontró un registro con la clave_municipio especificada.', 1;

        -- Actualizar log
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @fchInicioEje, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', @ENTITYID)
        WHERE IdLog = @ENTITYID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- Registrar error en log
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @fchInicioEje, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @id:', ISNULL(@ENTITYID, '')),
            ErrorMensaje = ERROR_MESSAGE()
        WHERE IdLog = @ENTITYID;

        THROW;
    END CATCH;
END;
GO

-- Ejecución de prueba
-- EXEC territorio.EliminarMunicipio @IdUsuario = 1, @clave_municipio = 101;



    Suscribirse a: Comentarios (Atom)