564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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.
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.
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.
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.
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.
|Parts of this blog|
25 Aytoun Street