MSSQL Help - Subquery in Insert?

[Phil99]

EDIT: Ignore all that crap, I was making it far too difficult for myself, doing it in code now



I've got a bit of an issue that I really want to solve but can't work out if it's even possible. When I try it as outlined below I get an error saying subqueries can't be used, only scalar values.

I have a system where as a payment is inserted, I'd like to work out the commission payable on the payment.

To work out commission I need to run a few subqueries to find out a Rep's commission rate and the amount of commission paid to them to date. In pseudo SQL:

Commission to Date:
SELECT SUM(commission) FROM Payments WHERE RepID = @repID
Commission:
@amount * (SELECT rate FROM Rep WHERE RepID = @repID) = commission
Rep Limit:
SELECT limit FROM Rep WHERE RepID = @repID

So the overall query I'm looking at creating would be along the lines of:

INSERT INTO [Payment] ([amount], [commission], [repID]) VALUES (@amount, 
CASE WHEN commission + commission_to_date > Rep.limit THEN
commission + commission_to_date - Rep.limit
ELSE
commission
END
,@repID)

It's all rather confusing so I'm wondering if I'm even going about this the right way, can anyone shed any light on using subqueries in inserts in MSSQL?

Thanks for looking