April 13, 2012 at 4:43 am
Hello,
While still getting to grips with T-SQL and all its nuances, I have come across something that slightly puzzles me.
It is to do with the DML UPDATE statement and the order in which values are set based on their sequencing within the SET clause.
As an example, I have written a little function to do a lookup of a table based on two supplied parameters. The function uses these parameters to return a value.
OK, so here's my sample UPDATE statement:
UPDATE TempSales2
SET VATRateID = 1,
Gross = dbo.UDF_ApplyVAT(VATRateID,Price)
WHERE Price = 2.77;The VATRateID column as you can see is being set with a new value before the statement reaches the function that uses that value as one of its parameters.
However, the function does not appear to be using the newly set VATRateID column value to generate its returned value. If I run the above statement a second time, then I can see the updated Gross column value.
I had assumed that by placing the setting of the VATRateID column before the calling of the function that uses it, that this would ensure the function used the new value, but it obviously doesn't.
It seems rather odd to me that this should work that way, but then again, I am new to all this, so not understanding why things placed in a particular sequence do not behave as expected is par for the course.
I know I could break the UPDATE into two smaller ones each doing one of the two above operations, but this appears somewhat crude - or at least a bit inefficient.
Is there a way of guaranteeing that the sequence in which I set columns within an UPDATE statement is assured, or am I missing something here?
Regards
Steve
April 13, 2012 at 5:00 am
SQL won't let you do that. The only way to guarantee order is to have two different UPDATE statements, or to fix your current one to pass in the hard value of the first column.
UPDATE TempSales2
SET VATRateID = 1,
Gross = dbo.UDF_ApplyVAT(1,Price)
WHERE Price = 2.77;
You don't have to set Price to a hard value because you're not setting it in the UPDATE statement.
April 13, 2012 at 5:03 am
Why not use a variable first?
something like:
DECLARE @VATRateID INT;
SET @VATRateID = 1;
UPDATE TempSales2
SET VATRateID = @VATRateID,
Gross = dbo.UDF_ApplyVAT(@VATRateID,Price)
WHERE Price = 2.77;
April 13, 2012 at 5:05 am
Forgot about the variable option. I retract the "the only way" part of my earlier post.
April 13, 2012 at 5:35 am
Bingo!! 🙂
I knew there had to be a slicker way of achieving what I want.
Many thanks for the excellent tip! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply