Create a stored procedure named spBalanceRange that accepts three optional param

ID: 3938824 • Letter: C

Question

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.

Explanation / Answer

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

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

Navigate

Create a stored procedure named spBalanceRange that accepts three optional param

Create a stored procedure named spDateRange that accepts two parameters, @DateMi

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Create a stored procedure named spBalanceRange that accepts three optional param

Question

Explanation / Answer

Related Questions

Navigate