SQL Question 22

Problem

  • Use AP
  • Create a stored procedure names uspBalanceRange that accepts 3 optional paramters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice. The balance is calculated as InvoiceTotal - CreditTotal - PaymentTotal
  • Parameters: @VendorVar, @BalanceMax, @BalanceMin
  • If the procedure is called with no parameters or @BalanceMax is 0 then the procedure should return all invoices where there is a balance due. Otherwise return invoices whose total is between @BalanceMin and @BalanceMax.
  • @VedorVar is used in a LIKE clause so if a user passes a value of ‘E%’, the program will find all vendors that start with ‘E’

Code

USE AP
GO
IF OBJECT_ID ('spBalanceRange') IS NOT NULL
DROP PROCEDURE spBalanceRange;
GO
CREATE PROC spBalanceRange
@VendorVar varchar(50) = '%',
@BalanceMin money = 0,
@BalanceMax money = 0
AS
IF @BalanceMax = 0
BEGIN
SELECT VendorName, InvoiceNumber,
InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE VendorName LIKE @VendorVar AND
(InvoiceTotal - CreditTotal - PaymentTotal) > 0 AND
(InvoiceTotal - CreditTotal - PaymentTotal) >= @BalanceMin
ORDER BY Balance DESC
END
ELSE
BEGIN
SELECT VendorName, InvoiceNumber,
InvoiceTotal - CreditTotal - PaymentTotal AS Balance
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE VendorName LIKE @VendorVar AND
(InvoiceTotal - CreditTotal - PaymentTotal) > 0 AND
(InvoiceTotal - CreditTotal - PaymentTotal)
BETWEEN @BalanceMin AND @BalanceMax
ORDER BY Balance DESC
END

Follow Up Problem

Code three calls to the procedure created in exercise 2:
a. Passed by position with @vendorVar = ‘Z%’ and no balance range
b. Passed by name with @vendorVar omitted and a balance range from $200 to $1000
c. Passed by position with a balance due that is less than $200 filtering for vendors whose names begin with C or F.

--a
EXEC spBalanceRange 'Z%'
--b
EXEC spBalanceRange @BalanceMin = 200, @BalanceMax = 1000
--c
EXEC spBalanceRange '[C,F]%', 0, 200

Notes

I am not getting any results with the test scripts. I might have bad or missing data in my database.

No Comments Yet.

Leave a comment