Fundamentos de Bases de Datos: Módulo 02: Ejemplos de Procedimientos Almacenados

-- ===================================

-- Fecha: 2016/11/11

-- ===================================

USE CSDaniel;

GO

-- Información Tarjetas

SELECT *

      FROM Tarjeta;

-- Información de los clientes y tarjetas

SELECT *

      FROM vClienteTarjeta;

-- Información de las Tarjetas y sus Saldos

SELECT *

      FROM vTarjetaSaldo;

-- ===================================

-- paActualizarSaldo

-- ===================================

USE [CSDaniel]

GO

/****** Object:  StoredProcedure [dbo].[paActualizarSaldo]    Script Date: 11/11/2016 12:54:26 p. m. ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Daniel Santizo Saldaña

-- Create date: 2016-11-11

-- Description:   Actualizar el saldo de una tarjeta y la fecha

-- =============================================

CREATE PROCEDURE [dbo].[paActualizarSaldo]

      -- Add the parameters for the stored procedure here

      @CodTarjeta int = 0

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- ACTUALIZAR EL SALDO de la Tarjeta @CodTarjeta

      DECLARE @Saldo Decimal;

      -- RECUPERA EL SALDO

      SELECT @Saldo = Saldo

            FROM vTarjetaSaldo

            WHERE CodTarjeta = @CodTarjeta;

      -- ACTUALIZA EN TARJETA

      UPDATE Tarjeta

            SET SaldoActual = @Saldo, FechaSaldo = GETDATE()

            WHERE CodTarjeta=@CodTarjeta;

END

GO

-- Actualizar el Saldo de la Tarjeta CodTarjeta = 1589

EXEC paActualizarSaldo 1589;

-- Consultar información

SELECT *

      FROM Tarjeta

      WHERE CodTarjeta=1589;

-- Información de Tarjetas y sus Saldos

SELECT T.CodCliente,

      v.CodTarjeta, v.CargoTotal, v.AbonoTotal,

      v.Saldo

      FROM vTarjetaSaldo v

      INNER JOIN Tarjeta T

      ON T.CodTarjeta = v.CodTarjeta

      ORDER BY T.CodCliente, v.CodTarjeta;

-- VISTA vClienteSaldo

SELECT *

      FROM vClienteSaldo

      ORDER BY CodCliente, CodTarjeta;

-- UPDATE / SELECT

SELECT T.CodCliente, T.CodTarjeta,

      T.FechaSaldo, T.SaldoActual,

      vCS.CodCliente, vCS.CodTarjeta,

      vCS.Saldo

      FROM Tarjeta T

      INNER JOIN vClienteSaldo vCS

            ON T.CodTarjeta = vCS.CodTarjeta

      WHERE T.CodCliente = 2;

-- ===================================

-- vClienteSaldo

-- ===================================

USE [CSDaniel]

GO

/****** Object:  View [dbo].[vClienteSaldo]    Script Date: 11/11/2016 12:52:57 p. m. ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[vClienteSaldo]

AS

SELECT dbo.Tarjeta.CodCliente, dbo.vTarjetaSaldo.CodTarjeta,    

      dbo.vTarjetaSaldo.CargoTotal, dbo.vTarjetaSaldo.AbonoTotal,

      dbo.vTarjetaSaldo.Saldo

      FROM dbo.Tarjeta

      INNER JOIN dbo.vTarjetaSaldo

      ON dbo.Tarjeta.CodTarjeta = dbo.vTarjetaSaldo.CodTarjeta

GO

GRANT SELECT ON [dbo].[vClienteSaldo] TO [ServicioCliente] AS [dbo]

GO

-- ===================================

-- paActualizarCliente

-- ===================================

USE [CSDaniel]

GO

/****** Object:  StoredProcedure [dbo].[paActualizarCliente]    Script Date: 11/11/2016 12:54:32 p. m. ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Daniel Santizo Saldaña

-- Create date: 2016-11-11

-- Description:   Actualizar los Saldos de las Tarjetas de un Cliente

-- =============================================

CREATE PROCEDURE [dbo].[paActualizarCliente]

      -- Add the parameters for the stored procedure here

      @CodCliente int = 0

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    -- Insert statements for procedure here

      UPDATE Tarjeta

            SET FechaSaldo = GETDATE(),

                  SaldoActual = vClienteSaldo.Saldo

            FROM vClienteSaldo

            WHERE Tarjeta.CodCliente = @CodCliente

            AND Tarjeta.CodTarjeta = vClienteSaldo.CodTarjeta;

END

GO

-- Clientes y Tarjetas

SELECT C.CodCliente, C.Apellido, C.Nombre,

      T.CodTarjeta, T.FechaSaldo, T.SaldoActual

      FROM Cliente C

      INNER JOIN Tarjeta T

      ON C.CodCliente = T.CodCliente;

-- Cliente = 1

EXEC paActualizarCliente 1;

-- Genera un script para ejecutar la actualización de saldos de todos

-- los clientes en la tabla.

SELECT 'EXEC paActualizarCliente ', CodCliente,

      ';'

      FROM Cliente

-- =============================================

-- SEGURIDAD

-- =============================================

USE [master]

GO

CREATE LOGIN [Servicio2]

      WITH PASSWORD=N'12345',

      DEFAULT_DATABASE=[CSDaniel],

      DEFAULT_LANGUAGE=[Español],

      CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [CSDaniel]

GO

CREATE USER [Servicio2]

      FOR LOGIN [Servicio2]

GO

USE [CSDaniel]

GO

ALTER ROLE [ServicioCliente]

      ADD MEMBER [Servicio2]

GO