Create a stored procedure named spBalanceRange that accepts

Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice with a balance due, sorted with largest balance due first. The parameter @VendorVar is a mask that’s used with a LIKE operator to filter by vendor name, as shown in figure14-5. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balance due. If called with no parameters or with a maximum value of 0, the procedure should return all invoices with a balance due.

Solution

Here let us take the maximun value as 9

The following code is as follows:

CREATE PROC spBalanceRanges

@VendorVar varchar(50) = \'%\',

@BalanceMin money = 0,

@BalanceMax money = 0

AS

declare @NBalanceMax Money

if @BalanceMax = 9 or @BalanceMax = 0

set @NBalanceMax = 999999999999

else

set @NBalanceMax = @BalanceMax

Select VendorName,InvoiceNumber,InvoiceTotal-CreditTotal - PaymentTotal AS Balance

FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

Where ((InvoiceTotal-CreditTotal - PaymentTotal) Between @BalanceMin AND @NBalanceMax)

AND (VendorName LIKE @VendorVar)

ORDER BY Balance