Using dynamic SQL for reporting process

What is the user requirements for the report?

• Lots of criteria on the screen but most of them are optional.
• The final result is a subset of a result.
• User allows to choose different sorting order.
• User allows to choose different grouping.
• The fields in the report was specified by the user at runtime.

By using dynamic SQL, you will be able to compose the appropriate SQL statement and then meets the user expectation. Another thing is that, you don't need to have many static SQL statements in the stored procedure.