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.

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

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:

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.