Help with procedure

Greetings,
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,
)
AS
BEGIN

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:

dbo.Track
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.

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.