i've a problem in a stored procedure when i try ovverriding a variable with the value of @@Identity of the last operation. I'll write an example:
DECLARE out_result BIGINT(20);
SET out_result = -1;
UPDATE tableName SET name = "pippo" WHERE id = 12;
SELECT @@Identity INTO out_result;
-- here it works great and out_result value is 12
IF out_result > 0 THAN
UPDATE otheTableName SET address = "Route 666" WHERE id = 37;
SELECT @@Identity INTO out_result;
END IF;
I expect that if the first operation was successfull, the variable out_result is greater than 0 and now the code will do the second update and it's so. After the second update (but it may be also a delete for example) i expect that out_result variable will take the value of the second update (in this example out_result = 37) but it's not so. Here out_result is still equal to 12. Some idea will be appreciated. Thanks.
First of all there is no
@@Identity
inMySQL
. You must have mistaken and probably talking aboutSQL Server
. Coming to your case, if you are still gettingout_result = 12
then I believe the correspondingUPDATE
statement (as pointed below) didn't processed any row because the conditionWHERE id = 37
didn't matched (OR) have returnedFALSE
. You can check and verify the same using@@ROWCOUNT
system global variable.