This page has 0 threads | Add post

Picture depicting wise owl guides and tutorials

Dynamic SQL

Written by Andrew Gould

In this tutorial

Executing a Basic String of Text
Using a System Stored Procedure to Execute a String
What's the Difference?
What's the Point?

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 tutorial explains how concatenation works in SQL Server.

Previous
tutorial

Triggers

Previous
part

Top of page

Next
part

Concatenation in SQL

Next
tutorial

Transactions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post