Post reply

Stored Procedure

  • January 27, 2011 at 4:49 pm

    #244371

    Hi,

    I started learning stored procedures and was stuck with a problem.

    The stored procedure requirement is as follows.

    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

    IF @BalanceMax=0 OR @BalanceMax IS NULL OR @BalanaceMin IS NULL

    SELECT @BalanceMax = InvoiceTotal-CreditTotal - PaymentTotal >= 0 FROM Invoices // This is the problem

    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 27, 2011 at 8:46 pm

    #1279489

    Presume this is the error message returned:

    Msg 102, Level 15, State 1,

    Incorrect syntax near '>'.

    Change that portion of your select statement from:

    IF @BalanceMax=0 OR @BalanceMax IS NULL OR @BalanaceMin IS NULL

    SELECT @BalanceMax = InvoiceTotal-CreditTotal - PaymentTotal >= 0 FROM Invoices

    To:

    SELECT VendorID, InvoiceTotal-CreditTotal - PaymentTotal AS 'BalanceDue' FROM Invoices

    WHERE InvoiceTotal-CreditTotal - PaymentTotal > 0

    Now to get tested help please post table definition(s), sample data as shown in the article referenced by clicking on the first link in my signature block.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply