Hi there,

UPDATE tbl_product SET @ActualOrderQty = CASE WHEN Qty > @OrderQty THEN @OrderQty ELSE Qty END, Qty = Qty - @ActualOrderQty WHERE ProductID = @ProductID;

Is it possible to do this with LLBLGen or do I need to user a sproc?

Cheers, Ian.

Don't have an answer, but I've not seen sql like this before.

What database is this?

What would this do to tbl_product if you ran this sql?

Any reason why this has to be done in SQL ? Can't you just implement the logic in C# and update and save an entity with the result of the calculation ?

Matt

What database is this?

Its for SQL Server. I didn't write it though.

What would this do to tbl_product if you ran this sql?

If I want to add 4 items to my shopping cart and there are 10 available then the product's stock count will be updated to 6.

If I want to add 4 items to my shopping cart but there are only 3 available then the product's stock count will be reduced by 3 (to zero) and the number of items that I'm able to add to my cart will be returned in @ActualOrderQty.

Can't you just implement the logic in C# and update and save an entity with the result of the calculation ?

Yes but doing this in one UPDATE saves me from having to SELECT the product row with an UPDLOCK followed by an UPDATE.

I think I could also use an OUTPUT clause!

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Don't use UPDLOCK, it's a killer for DB scalabilty and performance. Use optimistic concurrency - this is exactly the scenario it is designed for.

Matt

From Wikipedia...

OCC is generally used in environments with low data contention.

However, if conflicts happen often, the cost of repeatedly restarting transactions hurts performance significantly; other concurrency control methods have better performance under these conditions.

Surely lots of people all trying to buy a product at the same time and who thus all need to read and update the product's stock value is a high data contention environment?

The reason I'm doing this work is because the last time we tried selling our event tickets on the web it sold out in a few minutes (perhaps that's an exageration ) and overwhelmed the crude stock control mechanism in place.

Personally I think this is something for an sproc or an afterUpdate trigger. You can manage the update expressions in LLBLGen, but not the output parameter. As for the connector, the only returned value is the number of affected rows. So either you do that in code, or you write and call an sproc.