Introduction to dynamic SQL

 In SQL, T-SQL

The idea of dynamic SQL

The idea of using dynamic SQL is to execute SQL, which will potentially generate and execute another SQL statement. While querying data you might want to dynamically set columns you would like to query. On the other hand you might want to parametrize table on which you want to operate.

The first idea one might come up with is to use variables and set them as required column name or table name. Hovewer such approach is not supported by T-SQL.

 

T-SQL does not permit to replace many parts of code with variables, for example:

  • Table name (FROM clause)
  • Database name (USE clause)
  • Column names (SELECT, WHERE, GROUP BY, HAVING, ORDER BY clauses)
  • Lists (IN, PRIVOT clauses)

 

Dynamic SQL Examples

The solution is to use dynamic SQL. But what it is in practice? In short, it is all about executing query as string.

Example of putting query to string:

Example of executing query, which is in string – dynamic SQL:

 

So, as we can see: EXEC statement is used to dynamically execute query which is stored in nvarchar variable. So let’s go back to example with dynamically choosing which columns from which table we would like to query. The solution for this might look like this procedure:

Which you can execute like every other T-SQL procedure:

 

As last example, let’s create procedure which will allow user to query all data from selected table with selected predicate in WHERE clause.

 

Dynamic SQL gives you more possibilites

In T-SQL you might also execute dynamic SQL with sp_executesql stored procedure, which is an alternative to EXEC. It allows you to use parameters – both input and output. It is generally better than EXEC in case of performance, because SQL Server might reuse cached execution plan.

 

Mateusz
Creator & author of blog.
Recommended Posts
Comments
  • Charise Capone

    Wow! This blog looks just like my old one! It’s on a totally different topic but it has pretty much the same page layout and design. Great choice of colors!

Contact Me

You can contact me with this form. :)

Not readable? Change text.