Getting the Id of a row I updated in Sql Server

Question

I'm trying to return the Id of a row I update in sql

UPDATE ITS2_UserNames
  SET AupIp = @AupIp
  WHERE @Customer_ID = TCID AND @Handle_ID = ID

  SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID

and I keep getting Null for the ID, how can I get this?

Answer 1

The @@identity and scope_identy() will hand you the identity of a new row, ie. after an insert. After your update, the identity of the row is... @Customer_ID or @Handle_Id? If is a different field, you shoudl use the OUTPUT clause to return the ID of the updated row:

UPDATE ITS2_UserNames  
SET AupIp = @AupIp  
OUTPUT INSERTED.PrimaryKeyID
WHERE @Customer_ID = TCID AND @Handle_ID = ID

Answer 2

If you want to find out the records that match that update you should do a select with it

Select IdColumn
From ITS2_UserNames
WHERE @Customer_ID = TCID AND @Handle_ID = ID

Answer 3

Aren't you identifying the IDENTITY value of the row already with @Handle_ID? If so, then just use:

  SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, @Handle_ID AS ID;

If not, then can you explain which column is the IDENTITY column?