Dynamic SQL can be used, when the syntax of a statement is only known at runtime. The statements will build typically by concatinating variables and strings. There are a lot of statements which can be prepared dynamically. A list of all this statements you can find here.
Dynamic cursors and dynamic calls to a stored procedure are supported!
Dynamic SQL means that the statements must be prepared. That means the syntax and semantic must be checked, the query must be optimized, etc.
There are no SELECT or VALUES statements in the prepared statement allowed.
The privileges to execute the statements in the dynamic SQL will be checked at runtime. Be sure that the user which is calling the stored procedure has enougth privileges to perform the dynamic SQL statement.