T-SQL language – Executing dynamic T-SQL instructions in Microsoft SQL Server

T-SQL is an extension of the SQL language that is used in Microsoft SQL Server. This section contains materials for learning Microsoft SQL Server and T-SQL language. This is one of my favorite sections on this site, as I specialize in T-SQL and Microsoft SQL Server. So there are a lot of articles collected here, and they are constantly added.

In this material we will talk about running dynamic T-SQL code, you will learn how to form a text string containing SQL instruction and run it for execution in Microsoft SQL Server.

Dynamic code in Microsoft SQL Server

Microsoft SQL Server allows you to execute SQL instructions generated dynamically, because sometimes you just can’t do without it. For example, to dynamically execute instructions that at first glance seem static. For example, the PIVOT operator, its syntax, suggests manually enumerating output columns, the number and name of which may be simply unknown to us in advance. But we can form a dynamic instruction, which will automatically recognize and substitute all the values we need in the SQL query, and thus we no longer need to know, let alone manually specify the output columns, in the case of PIVOT operator, by the way, I have previously given an example of a dynamic PIVOT.

Dynamic SQL instruction – it’s just a text string, which after conversion and substituting all values is executed by SQL server as a normal SQL instruction.

Thus, to form a dynamic SQL instruction, you just need to form a text string with the necessary variables, the values of which you want to substitute, or to make a string concatenation with the variables using the + (plus) operator.

In Microsoft SQL Server there are two ways to run strings containing SQL instructions: the EXECUTE command and the sp_executesql system storage procedure.

Source data for the examples

Before moving on to examining examples of dynamic instruction execution, let’s create test data, such as a TestTable table, and add a few rows to it.

-Creation of a table
CREATE TABLE TestTable(
[ProductId] [INT] IDENTITY(1,1) NOT NULL,
[CategoryId] [INT] NOT NULL,
[ProductName] [VARCHAR](100) NOT NULL,
[Price] [Money] NULL
)
GO
-Include data in the table
INSERT INTO TestTable
VALUES (1, 'Keyboard', 100),
(1, 'Mouse', 50),
(2, 'System Block', 200)
GO
--data sampling
SELECT * FROM TestTable

EXECUTE command in T-SQL

EXECUTE (short for EXEC) – a command to run stored procedures and SQL instructions as text strings.

Before turning to the examples, it should be noted that using dynamic code using EXEC command is not safe! The fact is that in order to form a dynamic SQL instruction, you must use variables for dynamically changing values. So, if these values will come from the client application, i.e. from the user, attackers can pass and, accordingly, introduce malicious code in our instruction as text, and we will simply execute it in the database, thinking that we were passed the usual parameters. Therefore, all such values should be checked very well before they are inserted into the instruction manual.

Example of using EXEC in T-SQL

Now you and I will form a dynamic SQL query, the text of which we will save in a variable, and then execute it using the EXEC command.

The query text will be stored in the @SQL_QUERY variable, the @Var1 variable will store the value that we will substitute in our query to make it dynamic (in our case we will manually assign a static value to a variable, although this value can be recognized, for example, with a query or some kind of calculation).

To form a string, we will use the string concatenation, namely the + (plus) operator, just understand that in this case the expressions involved in the operation must have a text data type. Variable @Var1 we will have INT data type, so to connect it to the string we will first convert its value to VARCHAR data type.

To make it clear what kind of SQL query we have, we’ll just see what we have in the @SQL_QUERY variable by the SELECT instruction.

-- Declare variables
DECLARE @SQL_QUERY VARCHAR(200),
@Var1 INT;

-Price the value to the variables
SET @Var1 = 1;

-Form an SQL instruction
SET @SQL_QUERY = 'SELECT * FROM TestTable WHERE ProductID = ' + CAST(@Var1 AS VARCHAR(10));

- Look at the summary line
SELECT @SQL_QUERY AS [TEXT QUERY]

-Executing a text string as an SQL instruction
EXEC (@SQL_QUERY)

Stored sp_executesql procedure in T-SQL

sp_executesql is a Microsoft SQL Server system storage procedure that executes SQL instructions. These instructions can contain parameters, thus making them dynamic.

The sp_executesql procedure has several parameters, the first parameter is the text of the SQL instruction, the second is the declaration of variables, the third and all subsequent ones are the transmission of values for the variables in the procedure and, accordingly, substitution in our instruction.

All parameters of the procedure sp_executesql must be passed in Unicode format (string data type must be NVARCHAR).

Example of using sp_executesql in T-SQL

In this example the final result will be exactly the same as in the example with EXEC, only dynamic values, we have this variable @Var1, we will declare and pass as parameters of the stored procedure sp_executesql.

-- Declare variables
DECLARE @SQL_QUERY NVARCHAR(200);

-Form an SQL instruction
SELECT @SQL_QUERY = N'SELECT * FROM TestTable WHERE ProductID = @Var1;';

- Look at the summary line
SELECT @SQL_QUERY AS [TEXT QUERY]

-Executing a text string as an SQL instruction
EXEC sp_executesql @SQL_QUERY,-SQL instruction text
N'@Var1 AS INT', -- Variable declaration @Var1
@Var1 = 1 -Show value for variable @Var1

T-SQL Tutorial. Dynamic SQL course: Exec and sp_executesql (video)