Introduction to Dynamic SQL

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 (FROM clause).
• Database name (USE clause).
• Column names (SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses).
• Lists (IN, PIVOT clauses).

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 WHERE  clause.

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.