January 27, 2011 at 4:50 pm
Hi,
I started learning stored procedures and was stuck with a problem.
The stored procedure requirement is as follows. This is taken from Murach book.
Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns a result set consisting of VendorName, InvoiceNumber, and Balance(InvoiceTotal-CreditTotal - PaymentTotal) 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. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters or with a max value of 9, the procedure should return all invoices with a balance due.
Now I was able to get most part of the question however I am not able to code the part of the question which is in bold. Following is what I have written. Thanks in advance for any kind of help.
CREATE PROC spBalanceRanges
@VendorVar varchar(50) = '%',
@BalanceMin money = 0,
@BalanceMax money = 0
AS
Select VendorName,InvoiceNumber,InvoiceTotal-CreditTotal - PaymentTotal AS Balance
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
Where ((InvoiceTotal-CreditTotal - PaymentTotal) Between @BalanceMin AND @BalanceMax)
AND (VendorName LIKE @VendorVar)
ORDER BY Balance
January 28, 2011 at 1:24 am
It's not very efficient, but it should do.
Prabably dynamic sql is the best tool here.
CREATE PROC spBalanceRanges
@VendorVar varchar(50) = '%',
@BalanceMin money = 0,
@BalanceMax money = 9
AS
Select VendorName,InvoiceNumber,InvoiceTotal-CreditTotal - PaymentTotal AS Balance
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
Where ((InvoiceTotal-CreditTotal - PaymentTotal) Between @BalanceMin AND CASE @BalanceMax WHEN 9 THEN ((InvoiceTotal-CreditTotal - PaymentTotal) ELSE @BalanceMax END)
AND (VendorName LIKE @VendorVar)
ORDER BY Balance
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply