hi Barry,
[quoted text, click to view]

nope... at least 2 separate DML query must be executed within the procedure
body..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply

Hi,

I have a sproc which returns a row from my table.

The table has a field which is to contain the date when the row was last
accessed ( ie. read) by the sproc.

Can my sproc return fields from the row, while causing an update to the
row's timestamp field, during the same hit on the db server?

Can I do this without causing the database server to execute the where
clause in both the sproc's Select and Update queries?

My select query is similar to this:
Select TOP(1) ID, <other fields>
From MyTable
Where ...
Order By ...

I am assuming that the DBMS has to do quite a lot of work to answer the TOP
part of this query. I was wanting to avoid a repeat of this work for the
timestamping part of the routine.

Things I have tried, but have problems with:
1> combining a variable assignment into the select clause, so that a
following Update query within the same sproc will know the row's ID. .. Not
allowed to combine data-retrieval with variable assignment.
2> have the select query create a CTE ("With" clause). I can then join this
CTE to the original table for the update, but this consumes the CTE so there
seems to be nothing there for a Select * query against the CTE to return my
rows back from the sproc.
3> seeing whether I can get a trigger to fire.

Any advice would be appreciated.

Is it that I don't need to worry about the Where clause being included in a
sproc's Select and Update queries because the DBMS will have cached the
answer somehow?

thanks
Barry Mossman