How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)