Updating Values During SELECT Using CTE And Row_Number()

Hi,

I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement.

I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'."

The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?

 

CREATE PROCEDURE [zk_update_request_england]
(
@property_type     tinyint,
@market_status     tinyint,
@price             int,
@bedrooms          tinyint,
@search_location   varchar(30),
@search_district   varchar(30),
@PageSize   int,
@PageIndex  int
)

AS

BEGIN

WITH SearchResults AS
(

   UPDATE dbo.zk_request_england
   SET    searched = searched + 1
   WHERE  property_type = @property_type
   AND    market_status = @market_status
   AND    bedrooms = @bedrooms
   AND    search_location = @search_location
   AND    search_district = @search_district
   AND    min_price <= @price
   AND    max_price >= @price 

   SELECT user_id,
          min_price,
          max_price,
          property_description,
          searched,
          ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber
   FROM   dbo.zk_request_england
   WHERE  property_type = @property_type
   AND    market_status = @market_status
   AND    bedrooms = @bedrooms
   AND    search_location = @search_location
   AND    search_district = @search_district
   AND    min_price <= @price
   AND    max_price >= @price

)

   SELECT user_id,
          min_price,
          max_price,
          property_description
   FROM   SearchResults
   WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END

 

Thanks in advance for any help.