jueves, 14 de noviembre de 2013

Mantenimiento básico de datos a través de procedimientos almacenados

En muchas ocasiones es mejor crear un procedimiento almacenado directamente en la Base de Datos SQL Server que escribir el código en un lenguaje de programación, de este modo en el código bastará con llamar al procedimiento almacenado con exec de esta forma:  exec sp_MantenimientoTabla '0001', 'Nueva Linea' Donde 0001 es el código y Nueva Línea la descripción.

Además de tener el código fuente mucho más limpio, si hay algún problema o es necesaria una modificación, sólo habrar que tocar el procedimiento almacenado con lo que no será necesario tocar el código fuente.


El procedimiento almacenado para el mantenimiento básico de una tabla (añadir, modificar) en este caso una tabla mínima (código y Descripción) quedaría del siguiente modo:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [PROPIETARIO].[sp_MantenimientoTabla]
  @Codigo         varchar(10),
  @Descripcion          varchar(255)
AS
if exists (Select strCodigo   From  PROPIETARIO.Nombre_Tabla Where strCodigo =@Codigo)
      -- Si existe lo actualiza. Si no, lo crea.
BEGIN
      IF @Descripcion IS NOT NULL

      UPDATE PROPIETARIO.Nombre_Tabla SET strDescripcion = @Descripcion
      WHERE strCodigo = @Codigo
     
END
ELSE
      INSERT INTO PROPIETARIO.Nombre_Tabla
            VALUES ( @Codigo, @Descripcion )
GO

Si ejecutamos el procedimiento dos veces y las dos veces pasa por el INSERT INTO dará un error de tipo:
Violation of PRIMARY KEY constraint 'PK_Primary_Key'. Cannot insert duplicate key in object 'Propietario.strCodigo'.


Para evitar esto delante del INSERT INTO añadimos esto: 



IF (select count(*) FROM Propietario.Nombre_Tabla WHERE strCodigo = @Codigo AND strDescripcion = @Descripcion)=0

BEGIN        
INSERT INTO PROPIETARIO.Nombre_Tabla
        VALUES ( @Codigo, @Descripcion )
END   

Procedimiento almacenado para mantenimiento básico (eliminar)  una tabla con un único código como clave.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [PROPIETARIO].[sp_EliminarFila] (
      @Codigo           char(10))

AS
      DECLARE @Valor integer
      Set @Valor=0

      If Exists (SELECT top 1 strCodigo FROM PROPIETARIO.Nombre_Tabla   WHERE          strCodigo = @Codigo )
      Set @Valor =1    
      If @Valor =0
      BEGIN
If Exists ( SELECT top 1 Nombre_Tabla FROM PROPIETARIO.Nombre_Tabla WHERE strCodigo= @Codigo )
            Set @Valor =1    
      END
      If @Valor=0
            DELETE PROPIETARIO.Nombre_Tabla WHERE strCodigo=@Codigo
      IF @@ERROR <> 0
            SELECT 1
      ELSE
            SELECT @Valor

Observese que se ha usado la palabra clave CREATE PROCEDURE si el procedimiento ya está creado usaremos ALTER PROCEDURE para modificarlo.

Ejemplo:

Seguimos con el ejemplo del Hospital, para crear un procedimiento almacenado (stored procedure sp) para añadir, modicar datos en la tabla tbAtiende escribimos este código en el analizador de consultas

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_SalvarDatos]
  @Codigo         int,
  @ID_MED         char(5),
  @ID_PAC         char(5)
AS
if exists (Select intID   From  dbo.tbAtiende Where intID = @Codigo)
      -- Si existe lo actualiza. Si no, lo crea.
      BEGIN
      IF @Codigo IS NOT NULL
                  UPDATE dbo.tbAtiende
                        SET strID_MED = @ID_MED,
                strID_PAC = @ID_PAC
                        WHERE intID = @Codigo
        END
  ELSE
   IF (select Count(*) FROM dbo.tbAtiende WHERE strID_MED = @ID_MED AND strID_PAC = @ID_PAC)=0

BEGIN   
      INSERT INTO dbo.tbAtiende VALUES ( @ID_MED, @ID_PAC )
END 
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

Y pulsamos el botón ejecutar.


crear un procedimiento almacenado

Hecho esto, si no da errores se creará, y al refrescar lo podremos ver en el explorador de objetos

mantenimiento de bases de datos con procedimientos almacenados

A partir de este momento para cualquier modificación hay que ejecutarlo igual que antes pero poniendo ALTER PROCEDURE en vez de CREATE PROCEDURE.

Para ver o modificar el código pulsamos el botón derecho del ratón sobre el objeto y hacemos:

añadir, modificar y eliminar datos con procedimientos almacenados


Donde se abrirá una ventana nueva con el código del procedimiento almacenado que podremos modificar de nuevo. Para guardar cambios, pulsamos otra vez el botón ejecutar.

3 comentarios:

  1. estimado puede pasarme la base de hospital que trabajan para los ejercicios?

    ResponderEliminar
  2. Ok, si dime donde te la envío. Ocupa 1,6 Mb es pequeña.

    ResponderEliminar
  3. te paso mi correo para que me remitas hilarioesteban@gmail.com y hacer las pruebas

    ResponderEliminar