The idea of using dynamic SQL is to execute SQL that 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 tables on which you want to operate.
The first idea one might come up with is to use variables and set them as required column names or table names. However, such an approach is not supported by T-SQL.
DECLARE @tablename AS NVARCHAR(255) = N'dbo.Table'; SELECT * FROM @tablename -- this code will fail
T-SQL does not permit replacing many parts of code with variables. For example:
- Table name (
- Database name (
- Column names (
- Lists (
Dynamic SQL Examples
The solution is to use dynamic SQL. But what it is in practice? In short, it is all about executing queries as strings.
An example of putting the query to the string:
DECLARE @query AS NVARCHAR(255) = N'SELECT * FROM dbo.Table'; SELECT @query AS query;
An example of executing the query, which is in the string (dynamic SQL):
DECLARE @query AS NVARCHAR(255) = N'SELECT * FROM dbo.Table'; EXEC(@query);
So as we can see, the
EXEC statement is used to dynamically execute the query that is stored in the
nvarchar variable. Let’s go back to the example with dynamically choosing which columns from which table we would like to query. The solution for this might look like this procedure:
IF OBJECT_ID('dbo.queryData', 'P') IS NOT NULL DROP PROC dbo.queryData; GO CREATE PROC dbo.queryData @tablename AS NVARCHAR(255) ,@columnnames AS NVARCHAR(255) AS BEGIN DECLARE @SQLString AS NVARCHAR(MAX); SET @SQLString = N'SELECT ' +@columnnames+N' FROM ' + @tablename; EXEC(@SQLString); END
...which you can execute like every other T-SQL procedure:
EXEC dbo.queryData 'dbo.Table', 'id, firstname, lastname, age'
As the last example, let’s create a procedure that will allow the user to query all data from the selected table with the selected predicate in the
USE TSQL2012; GO IF OBJECT_ID('dbo.queryData', 'P') IS NOT NULL DROP PROC dbo.queryData; GO CREATE PROC dbo.queryData @tablename AS NVARCHAR(255) ,@column AS NVARCHAR(255) ,@predicateOperator AS NVARCHAR(255) ,@predicateValue AS NVARCHAR(255) AS BEGIN DECLARE @SQLString AS NVARCHAR(MAX); SET @SQLString = N'SELECT * FROM ' + @tablename + N' WHERE ' + @column + @predicateOperator+@predicateValue ; EXEC(@SQLString); END EXEC dbo.queryData 'dbo.Table', 'age','>=','18'
Dynamic SQL Gives You More Possibilities
In T-SQL, you might also execute dynamic SQL with the
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 when it comes to performance because SQL Server might reuse cached execution plans.