Wow, that OUTPUT keyword is new to me. Thanks.

Followup question though, if you don't mind. My SELECT statement is pretty complex (used for paging through results in web page). Not sure I can use an UPDATE instead:

SELECT *

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 ASC

If I use the OUTPUT keyword as you suggest, can I put all this in the WHERE clause of an UPDATE statement? Yikes.

Thanks,

Matt