SIBD - S13.2

/*2-1*/
IF OBJECT_ID('spTotalFactura') IS NOT NULL
    DROP PROC spTotalFactura
GO
CREATE PROC spTotalFactura
    @Total      MONEY OUTPUT,
    @DATA       DATE
AS
SELECT @Total = SUM(TotalFactura)
    FROM Facturas
    WHERE DataFactura >= @DATA

/*2-2*/
DECLARE @TotalFac FLOAT
EXEC spTotalFactura @TotalFac OUTPUT, '2006-01-01'
PRINT 'Total das Facturas: ' + CONVERT(VARCHAR, @TotalFac)

/*2-3*/
IF OBJECT_ID('spFaturasEmDividaPorIDFornecedor') IS NOT NULL
    DROP PROC spFaturasEmDividaPorIDFornecedor
GO
CREATE PROC spFaturasEmDividaPorIDFornecedor
    @ID             INT
AS

DECLARE @tabela TABLE
(
    NomeFor     VARCHAR(100) ,
    Num         VARCHAR(100) ,
    DATA        DATE ,
    TotalFacDiv FLOAT
)
INSERT @tabela
SELECT  Nome, NúmeroFactura, DataFactura, TotalFactura
    FROM Facturas JOIN Fornecedores
        ON Fornecedor = IDFornecedor
    WHERE IDFornecedor = @ID AND TotalFactura - Pagamento - Crédito > 0
SELECT * FROM @tabela

/*2.4*/
EXEC spFaturasEmDividaPorIDFornecedor 122

/*2.5*/
IF OBJECT_ID('spAplicaIVA') IS NOT NULL
    DROP PROC spAplicaIVA
GO
CREATE PROC spAplicaIVA
    @Valor          MONEY,
    @TaxaIVA        VARCHAR(8),
    @ValorComIVA    MONEY OUTPUT,
    @ValorDoIVA     FLOAT OUTPUT
AS
IF @TaxaIVA = 'Normal'
    SET @ValorDoIVA = 0.23
ELSE IF @TaxaIVA = 'Reduzida'
    SET @ValorDoIVA = 0.06
SET @ValorComIVA = @Valor + @Valor * @ValorDoIVA

/*2.6*/
DECLARE @ValorD MONEY = 1000, @ValorCI MONEY, @IVA FLOAT
EXEC spAplicaIVA @ValorD, 'Normal', @ValorCI OUTPUT, @IVA OUTPUT
PRINT 'Valor:           ' + CONVERT(VARCHAR, @ValorD)
PRINT 'Valor c/IVa:     ' + CONVERT(VARCHAR, @ValorCI)
PRINT 'IVA:         ' + CONVERT(VARCHAR, @IVA*100) + '% [Normal]'
PRINT ' '
EXEC spAplicaIVA @ValorD, 'Reduzida', @ValorCI OUTPUT, @IVA OUTPUT
PRINT 'Valor:           ' + CONVERT(VARCHAR, @ValorD)
PRINT 'Valor c/IVa:     ' + CONVERT(VARCHAR, @ValorCI)
PRINT 'IVA:         ' + CONVERT(VARCHAR, @IVA*100) + '% [Reduzida]'

/*2-7*/
IF OBJECT_ID('spTopReg') IS NOT NULL
    DROP PROC spTopReg
GO
CREATE PROC spTopReg
    @Tabela VARCHAR(50),
    @NRegs INT = 5
AS
DECLARE @SQL AS VARCHAR(150)
SET @SQL = 'SELECT TOP ' +  CONVERT(VARCHAR, @NRegs) + ' * FROM ' + @Tabela
EXEC (@SQL)

/*2-8*/
EXEC spTopReg 'Projectos', 3