T-SQL 101: 126 Executing Dynamic SQL Statements in SQL Server T-SQL

It’s also possible to create the command dynamically before you execute it.

In the example above, I’ve set a number of different parts of a SQL statement into variables, and then used them to construct a complete SQL statement that I’ve then executed. I just have to create a valid SQL statement as a string.

Warning

While this might seem really, really convenient, and it can be incredibly useful, it is something you need to be extraordinarily careful with. One of the problems with this is that it can easily open you up to what’s called SQL injection attacks.

You should never take input from a user and then use it to construct a SQL statement that you then execute.

It’s simply not safe to do.

Should you ever use dynamic SQL?

I can’t just say no. Unfortunately, you might often need to. It’s particularly common if you need to create generic utilities.

One of the most frustrating parts of this, is that many statements and functions provided by the SQL Server team, will not accept variables as parameters.

And so constructing the statement to be executed by using dynamic SQL is the only option left to us.

I wish it wasn’t so.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-03-08