INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getdate()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor!
Woops. Problem solved, nm
|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegro ups.com...
> Woops. Problem solved, nm
>