SELECT and SET with ROWCOUNT

I have a SP with which I fetch a defined amount of rows. How can开发者_JAVA技巧 I change the value of a column in the fetched rows? Such as 'has been fetched' = 1.

Any advice?

EDIT: The SP looks something like this

SET ROWCOUNT @numberOfRows

SELECT * FROM tableA where notSent = 1

I would like to change the 'notSent' colum for all the rows that i fetch. Is this possible?

---

1) Don't use Select * in a stored procedure - always specifically list the fields required as shown below - replace field1,2,3 etc with the actual fields you want returned.

OK - Modified answer:

2) Set a flag on the columns you want to select and update with a value that will not be otherwise programmatically set - e.g. -1 - Then select these records, then update them to set the final value required. Doing it this way will avoid the possibility that you will update a different set of records to those selected, due to inserts occurring half-way through the stored procedure's execution. You could also avoid this by use of locks, but the way below is going to be far healthier.

 UPDATE Table set notSent = -1 WHERE notSent = 0

 SELECT ... from Table where notSent = -1

 UPDATE Table set notSent = 1 where notSent = -1