Results 1 to 2 of 2

Thread: Help with procedure

  1. #1
    Join Date
    Sep 2009

    Help with procedure

    I come from lazy developer land where I had a very nice DBA who would write stored procedures based on variables I would pass for data manipulation.

    Now, Iíve moved to hard-luck developer land where there are no nice DBAs and my SQL is a bit rusty and out of date.

    Iím hoping for a bit of help. Iím working on SQL 2008.

    At the time a payment is made, I have variables being passed called UserId and Amount.

    I have to get a percentage of this amount and insert that into another tracking table.

    Here is what I have:

    User table with columns: IsApproved (bit), IsLockedOut (bit)
    Rev_Perc table with columns: NumUsers (int), Perc (decimal 2,2)
    The Rev_Perc table is populated with these values:
    499, .37
    2499, .18
    9999, .14

    Upon getting passed the UserId and Amount, the procedure needs to get the number of active (IsApprove=True, IsLockedOut=False) records in the User table.

    @UserId int,
    @Amount money
    @NumUse int,

    SELECT COUNT(*) AS NumUse FROM Users
    WHERE IsApproved=True
    AND IsLockedOut=False

    I need to compare the NumUse to the Rev_Perc table and draw the appropriate Perc value. (e.g. NumUse = 2499 or less, greater than 499 = .18), then multiply that value by the Amount passed, then insert a record into another table as follows:

    UserId, (Passed)
    Amount, (Passed)
    NumUsers, (From Count)
    Perc, (Percentage from Rev_Perc)
    Co_Rev, (Amount * Perc = Co_Rev)
    Date_Time, (Date/Time record is generated)

    Thanks in advance for any willing to assist.

  2. #2
    Join Date
    Mar 2003
    Woking, UK
    To get the appropriate Perc value you may use following select

    SELECT TOP 1 @Perc = Perc FROM Rev_Perc WHERE NumUsers >= @NumUsers ORDER BY NumUsers

    you should consider scenario when your number of users is greater than max value register in Reg_Perc
    currently If you pass value greater than 9999 it will return empty result and that could be a problem for your insert statement.
    You Have To Be Happy With What You Have To Be Happy With (KC)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts