Getting the Id of a row I updated in Sql Server

The @@identity and scope_identity() 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 it is a different field, you should 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

I think what @pauloya tried to say is:

if you will update a table then you have a WHERE clause, so if you use that same where clause on a select with an INTO #tempTable you have all rows affected by your UPDATE.

So you can go:

SELECT
    userName.ID
INTO #temp
FROM ITS2_UserNames AS userNames
WHERE @Customer_ID = TCID AND @Handle_ID = ID

then you update

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

finally you can return all IDs affected by your update

SELECT * FROM #temp

You can do this with OUTPUT but you will have to declare a variable table like

DECLARE @tempTable TABLE ( ID INT );

and then you use OUTPUT

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

Tags:

Sql

Sql Server