Dynamic SQL
Part one of a four-part series of blogs

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.

  1. Dynamic SQL (this blog)
  2. Concatenation in SQL
  3. Building Dynamic SQL Statements
  4. SQL Injection Attacks

This blog is part of our complete SQL tutorial. You can also learn about this technique from our SQL Server training courses.

Posted by Andrew Gould on 22 April 2013

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.

This blog has 0 threads Add post