Can you call ROW_NUMBER() in dynamic sql and use Common Table Expressions in Dynamic Sql?

Question

I want to limit the number of records returned by calling ROW_NUMBER(), but I am curious as to if you can call it in dynamic sql?

Also, Can you use CTE's in dynamic sql?

Answer 1

Do you mean, as opposed to being called from inside a stored procedure or other code object? If that's the question, the answer is yes. Note for the record that row_number is only supported on SQL 2005 and later.