As the name implies dynamic SQL is creating a SQL script using another SQL script dynamically.
It is true we could use variables in SQL for a lot of times to pass values for WHERE clause but it is not always possible to use variables to meet our requirements. For example

This will be a good example where we could use variables but what if we have two reports one that need to return only a LastName and the other one both FirstName and LastName can we do this
–Example 1

Or what if we have other report that need to get our contact from different tables

–Example 2

Can we use a variable for the above two examples the answer is no. We can’t just use a variable to substitute objects like the name of database in USE statement, columns in SELECT statement, tables in FROM clause, in some cases combination of conditions in WHERE for example literals IN() function .
Okay now we have seen we can’t use variables in SQL so what will be our best bet to do this then? The answer will be using dynamic SQL.
Let us see how we could make the above two examples work in action:

Now that we have our dynamic SQL the next question will be how we can execute that?
The first method will be by using EXECUTE OR EXEC.
As you could see in example one you from line (-) a string literal, a string variable or a combination of the two could be used in side Exec() or You could just create another String Variable to hold all the dynamic SQL and put that variable inside Exec() as it shown in Example 2.
The other method to execute is by using sp_executesql could be used to execute your dynamic SQL and it is able to utilize previously cached execution plan (I will discuss about sp_executesql in different blog post ).
There are few things you need to be careful here:

• Make sure you give enough space for your query variable.
• If your string is more than 8000 characters don’t forget to break it to multiple variables.
• If you have single quote in your string literal that has to be escaped with another single quote.
• Don’t Forget to give a space and inside a string literal when you are joining it with variables

Let us see some of the most common mistakes users run into while writing their dynamic SQL

Common Error 1:

The variable that hold the dynamic SQL has only 20 characters long which is not enough to hold the entire statement.
Common Error 2:

Notice the where statement WHERE LastName IN (‘O”Neal’) every single has to be escaped with another single quote.
Common Error 3:

There is no enough space where the FROM clause ends there for the resulting string will become FROMtContact.
Read More:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709342(v=vs.85).aspx