SQL: Update a row and returning a column value with 1 query

Question

I need to update a row in a table, and get a column value from it. I can do this with

UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id;
SELECT Name FROM Items WHERE Id = @Id

This generates 2 plans/accesses to the table. Is possibile in T-SQL to modify the UPDATE statement in order to update and return the Name column with 1 plan/access only?

I'm using C#, ADO.NET ExecuteScalar() or ExecuteReader() methods.

Answer 1

Use a Stored procedure for this.

Answer 2

Create a stored procedure that takes the @id as a parameter and does both of those things. You then use a DbDataAdapter to call the stored procedure.

Answer 3

Accesses table only once :

UPDATE Items SET Clicks = Clicks + 1 , @Name = Name WHERE Id = @Id;
select @name;

Answer 4

You want the OUTPUT clause

UPDATE Items SET Clicks = Clicks + 1
OUTPUT INSERTED.Name
WHERE Id = @Id

Answer 5

If you're using SQL Server 2005 onwards, the OUTPUT clause is ideal for this

Answer 6

Is there any way to achive this for Access database?

I want to update a record and then get the value.

Thanks...