Dynamic SQL is a technique for building valid SQL statements from separate pieces of text. You can use this technique to create remarkably flexible and useful queries, as long as you're aware of the potential danger of SQL injection attacks.

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Dynamic SQL

Using dynamic SQL allows you to construct a string of text which can
then be executed as an SQL statement. There are several reasons why this
can be useful and one reason why it's potentially dangerous.

Executing a Basic String of Text

You can execute a string of text as an SQL statement by using the
EXECUTE or EXEC keywords:

EXEC ('SELECT * FROM tblFilm')

Executing the code above provides the same results as the SELECT statement
would have generated by itself.

Using a System Stored Procedure to
Execute a String

You can also use a system stored procedure called sp_executesql
to execute a string as a statement:

EXEC sp_executesql N'SELECT * FROM tblFilm'

Again, the results of executing this code are the same as those that would be
generated by the SELECT statement itself.

When using the sp_executesql stored procedure you must make
sure to pass the text as a unicode string. Hence the letter N
which precedes the string in the example above.

What's the Difference?

The main difference between the two methods described above is that the
sp_executesql stored procedure enables the use of
query plan
caching. In simple terms this means that using the stored procedure can
result in dynamic SQL which runs faster than simply using the EXEC
statement alone.

What's the Point?

So why is any of this useful? Well, the power in dynamic SQL comes from the
fact that you can build any SQL statement you want by concatenating strings of
text. This means that any part of a statement can be set using variables or
parameters. The next part of this blog series explains how concatenation works
in SQL Server.