Other than the final ORDER BY, I think the query below will do it.

Personally, I wouldn't rewrite your existing query any more than you have to to make it do what you need:

UPDATE doctors_update

SET NumImpressionsInSearches = NumImpressionsInSearches + 1

OUTPUT

derived.*

FROM dbo.Doctors doctors_update

INNER JOIN (

SELECT TOP 100 PERCENT *

FROM

(

SELECT

TotalRows = COUNT(*) OVER(),

RowNum =

CASE

WHEN @sortColumn = 'OccupationName_ASC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName)

WHEN @sortColumn = 'City_ASC' THEN ROW_NUMBER()OVER (ORDER BY D.City)

WHEN @sortColumn = 'OccupationName_DESC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName DESC)

WHEN @sortColumn = 'City_DESC' THEN ROW_NUMBER()OVER (ORDER BY D.City DESC)

ELSE ROW_NUMBER()OVER (ORDER BY NewID()) -- note this returns random order, and each page's results may not be unique.

END,

D.DoctorID, D.OccupationID, D.LastName, D.FirstName, D.Designations, D.NumRecommendations, D.City, D.State, D.Zip, D.Phone, D.NumProfileViews, D.ApprovedStatus, O.OccupationName, D.MembershipStatusID

FROM dbo.Doctors P WITH (NOLOCK)

INNER JOIN dbo.Occupations O ON O.OccupationID = D.OccupationID

INNER JOIN dbo.DoctorsPracticeAreas DPA ON DPA.DoctorID = D.DoctorID

WHERE DPA.PracticeAreaID = @practiceAreaID

AND (@occupationID IS NULL OR D.OccupationID = @occupationID)

AND (@name IS NULL OR D.LastName LIKE @name + '%' OR D.PracticeName LIKE '%' + @name + '%')

)

AS XYZ -- you need this AS XYZ

WHERE RowNum BETWEEN @startRow AND (@startRow + @rowsPerPage) - 1

ORDER BY RowNum

) AS derived ON

derived.DoctorID = doctors_update.DoctorID

Of course the "ORDER BY RowNum" is NOT guaranteed for the final result, so you can drop it if you prefer.

You could OUTPUT into a temp table and then SELECT from the temp table with ORDER BY ... but of course you'd have to create the temp table first, one way or another.