Here are some suggestions that might be wrong but I can't test performance without actual knowledge of your environment:

- Instead of using a CTE, use a temp table to insert the rows and use @@ROWCOUNT (as suggested by Gail) to count all the records you have.

- You could try changing the ROW_NUMBER() with an identity in the temp table.

- Follow Sean's advice on the way to create a catch-all-query by going to Gail's blog http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ (here's the link once again)

- Leave the order to the front-end. It seems that you have lots of options to order and your way seems messy and with possibility of problems if you want to add a new order.

- Drop the NOLOCK hint, it won't help over performance and might just give you inaccurate results.

-Don't believe someone is great just because it has several years of experience and has worked with big companies. I've done both things and that's not the reason I'm good (good but working on being great).

If you come for help, don't turn down just because you don't agree. This forum is full of talented and respected people that volunteer to help anyone.