Execute Stored Procedure in UPDATE statement

[rduncan10]

I'm new to SQL Server and I am having some trouble with something that seems to me should be quite easy. I want to update a field to the return value of stored procedure.

So the code looks sort of like this

UPDATE table1
SET totHours = EXECUTE sp_calcHours @hours OUTPUT
WHERE otHours > 16

That's a bit simplified (there should be input parameters too, but you get the idea I hope. The calculations in sp_calcHours are quite complex.

I'm working in an Access ADP file and it tells me "Incorrect Syntax Near EXECUTE".

Can this be done? The only other way around it I can see is to use a DECLARE LOCAL CURSOR loop to go through the entire table to do the calculation, but it seems to me it should be easier than that.

Thanks,
Rob

 

[SQLSister]

Bad idea, do not mention using a cursor for this.

Put the calculation inthe update statement.


Questions about posting. See faq183-874