30 Apr 2026 Dynamic SQL for Data Validations

Despite the rise of new technologies, SQL continues to prove its value in scenarios that demand efficiency and complexity, or where it remains the only practical option in the working environment. 

In this blog post we’ll explore the power of dynamic SQL, a technique that enables flexible query generation to address a range of challenges, from process automation to repetitive task execution. 

To demonstrate its potential, we’ll present a real-world use case in which dynamic SQL was used to automate the validation of data stored in a table and to identify any invalid records. 

We’ll also highlight the limitations of this approach, as well as key considerations to bear in mind before choosing dynamic SQL as part of a technical solution. 

But first, let’s clarify an important question: what exactly do we mean by dynamic SQL?

What is Dynamic SQL?

Dynamic SQL refers to SQL code that is generated and executed at runtime, rather than hard-coded within an application or script. Unlike static SQL, where queries are predefined before execution, dynamic SQL allows queries to be built flexibly, based on variable inputs or conditions. 

To illustrate this difference, we’ll start off with a basic example of static SQL versus dynamic SQL. Throughout this article, we’ll be using T-SQL, the language used in Microsoft SQL Server. 

Note: The code will be shown in sections to make the explanation easier to follow.

Example of static SQL:  

SELECT * FROM Employees WHERE Name = 'John'

Dynamic SQL:

Example 1

With dynamic SQL, however, we can change the employee name dynamically, depending on the value of the EmployeeName variable used in the query:

DECLARE @EmployeeName NVARCHAR(50),
        @SqlStatement NVARCHAR(MAX)

SET @EmployeeName = 'John'
SET @SqlStatement = CONCAT('SELECT * FROM Employees WHERE Name = ''', @EmployeeName, '''')

EXEC(@SqlStatement)

Example 2

As we can see, the EXEC function executes the SQL statement in the @SqlStatement variable.

The same result can also be obtained by using the sp_executesql system function. This method offers several advantages.

First, it supports both input and output parameters. This makes complex queries easier to maintain because it separates SQL logic from parameter values, improving readability and making the code easier to debug and modify. Additionally, because parameters are treated strictly as data, not as executable code, this approach significantly reduces the risk of SQL injection, which we will discuss later.

Secondly, sp_executesql supports execution plan reuse, which is especially useful for queries that run repeatedly, as it can improve performance by avoiding the need to recompile the query each time.

Below is the equivalent version of the previous example using sp_executesql:

DECLARE @EmployeeName NVARCHAR(50),
        @SqlStatement NVARCHAR(MAX)
   
SET @EmployeeName = 'John'  
SET @SqlStatement = 'SELECT * FROM Employees WHERE Name = @Name'
  
EXEC sp_executesql @SqlStatement, N'@Name NVARCHAR(50)', @Name = @EmployeeName

Example 3

Here the sp_executesql function is called after EXEC, followed by the variable containing the SQL statement; the parameter is then declared and assigned its value. This makes the SQL statement itself cleaner and easier to read.

In addition to supporting flexible queries using input parameters, sp_executesql also allows output parameters to return information from a dynamically executed query. Continuing with the same example, we can obtain the number of employees named “John”:

DECLARE @EmployeeName  NVARCHAR(50),
        @SqlStatement  NVARCHAR(MAX),
        @EmployeeCount INT

SET @EmployeeName = 'John'
SET @SqlStatement = 'SELECT @EmployeeCountOutput = COUNT(*) FROM Employees WHERE Name = @Name'

EXEC sp_executesql 
     @SqlStatement,
     N'@Name NVARCHAR(50), @EmployeeCountOutput INT OUTPUT', 
     @Name = @EmployeeName, 
     @EmployeeCountOutput = @EmployeeCount OUTPUT

SELECT @EmployeeCount AS EmployeeCount

Example 4

Now the @EmployeeCountOutput variable is used to store the count result inside the dynamic query, and the @EmployeeCount variable is assigned the count result outside the dynamic SQL.

This is how we can return the result of a dynamic query through an output parameter, which can later be used in our code. If we want to visualise the result, we can run a SELECT on @EmployeeCount.

These are the basic foundations for working with dynamic SQL.

Dynamic SQL, complete example code:

-- DDL for Employees Table
CREATE TABLE [dbo].[Employees] (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    Age DECIMAL(38,0),
    Mail NVARCHAR(50),
    Department NVARCHAR(50),
    HireDate DATE
);

-- Data for Employees Table
INSERT INTO [dbo].[Employees] (Name, Age, Mail,Department, HireDate)
VALUES 
('John', 32, 'john@gmail.com','Finance', '2018-03-15'),
(NULL, 28, 'unkown','Marketing', '2020-07-01'),
('Bob', 45, 'bob@gmail.com','IT', '2010-11-23'),
('Maria', 37, 'mariaagmail.com','HR', '2016-05-10'),
('Daniel', 30, 'daniel@gmail.com','Operations', '2022-01-05');

-- Example 1
 SELECT * FROM Employees WHERE Name = 'John' 

-- Example 2
 DECLARE @EmployeeName NVARCHAR(50), 
         @SqlStatement NVARCHAR(MAX) 

SET @EmployeeName = 'John' 
SET @SqlStatement = CONCAT('SELECT * FROM Employees WHERE Name = ''', @EmployeeName, '''')

EXEC(@SqlStatement)

-- Example 3
DECLARE @EmployeeName NVARCHAR(50), 
        @SqlStatement NVARCHAR(MAX) 

SET @EmployeeName = 'John' 
SET @SqlStatement = 'SELECT * FROM Employees WHERE Name = @Name' 

EXEC sp_executesql @SqlStatement, N'@Name NVARCHAR(50)', @Name = @EmployeeName 

-- Example 4
DECLARE @EmployeeName  NVARCHAR(50), 
        @SqlStatement  NVARCHAR(MAX), 
        @EmployeeCount INT

SET @EmployeeName = 'John' 
SET @SqlStatement = 'SELECT @EmployeeCountOutput = COUNT(*) FROM Employees WHERE Name = @Name' 

EXEC sp_executesql  
     @SqlStatement, 
     N'@Name NVARCHAR(50), @EmployeeCountOutput INT OUTPUT',  
     @Name = @EmployeeName,  
     @EmployeeCountOutput = @EmployeeCount OUTPUT 

SELECT @EmployeeCount AS EmployeeCount

A Real Use Case: Validating Data with Dynamic SQL

As mentioned earlier, dynamic SQL can also be applied to automate a validation process for data stored in a table.

The objective is to create a process that uses business rules (defined as any type of rule specified by a business user) to determine whether the data in a table is valid. Invalid data is then logged so issues can be tracked and preventive action can be taken to reduce future inconsistencies.

Dynamic SQL provides the flexibility required to validate data across different tables, making this a scalable approach that remains effective over time.

To define this process, we’ll begin by creating a configuration table called BUSINESS_RULES_VALIDATIONS, where business users define the validation rules, written in T-SQL, and the table to which those rules apply. The configuration table will look like this:

As will become clear later, the VALIDATION_RULE field contains the SQL code that will be included in the WHERE clause of the dynamic query.

It is also important to note that any record meeting the condition defined in the VALIDATION_RULE field will be considered invalid.

Once the configuration table has been built, we’ll create a stored procedure that uses dynamic SQL to perform each validation defined in the table. Any records that fail the validation will be logged in a separate table.

The stored procedure starts as follows:

CREATE PROCEDURE SP_VALIDATE_DATA AS

Then we’ll declare the variables required for the procedure:

BEGIN
       DECLARE @Schema                  NVARCHAR(10),
               @Table                   NVARCHAR(100),
               @RowCount                INT,
               @SqlStatement            NVARCHAR(MAX),
               @SqlStatementParameters  NVARCHAR(1000),
               @ValidationRule          NVARCHAR(MAX),
               @RuleWithQuotes          NVARCHAR(MAX),
               @ValidationRules         CURSOR,
               @IdentityColumn          NVARCHAR(100)  

Next we’ll initialise the ValidationRules cursor to iterate through the validation rules defined in the configuration table:

SET @ValidationRules = CURSOR LOCAL STATIC READ_ONLY FOR
            SELECT  [SCHEMA],
                    [TABLE],
                    [VALIDATION_RULE]
            FROM    [dbo].[BUSINESS_RULES_VALIDATIONS]

OPEN @ValidationRules

FETCH NEXT FROM @ValidationRules INTO @Schema, @Table, @ValidationRule

WHILE @@FETCH_STATUS = 0 

Before inserting invalid data into the log table, we’ll verify whether the current validation rule returns any invalid records. This is done automatically using the following dynamic SQL statement:

BEGIN
   SET @SqlStatementParameters = '@RowCountOutput INT OUTPUT'
          SET @SqlStatement = 'SELECT @RowCountOutput = COUNT(*) 
                               FROM' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) +
                               WHERE ' + @Validation		          
	  
          EXEC sp_executesql @SqlStatement, @SqlStatementParameters, 
               @RowCountOutput = @RowCount OUTPUT

          IF @RowCount > 0

This means that if no invalid records are found for a rule (RowCount = 0), the procedure will skip directly to the next validation specified in the configuration table. If invalid records are found, they will be written to a table called INVALID_DATA, as shown below:

In this table, ROW_ID stores the unique identifier of the record in the validated table, along with the table where the validation was performed, and the validation rule itself.

First, we’ll identify the identity column of the table being validated by running this query on the system tables. We’ll also adjust the validation rule string to avoid issues with rules that contain quotation marks:

             BEGIN
                SELECT      @IdentityColumn = COL.name 
                FROM        sys.all_columns COL
                INNER JOIN  sys.all_objects TBL
                ON          TBL.object_id = COL.object_id
                INNER JOIN  sys.schemas SCH
                ON          TBL.schema_id = SCH.schema_id
                AND         SCH.name = @Schema
                WHERE       TBL.name = @Table AND
                            COL.is_identity = 1

                SET @RuleWithQuotes = REPLACE(@ValidationRule, '''', '''''')

Finally, we’ll define the dynamic query used to insert the invalid records identified during validation:

SET @SqlStatement = CONCAT('INSERT INTO [dbo].[INVALID_DATA] 
                          (ROW_ID, SCHEMA, TABLE, VALIDATED_RULE) 
                           SELECT ', @IdentityColumn, ', 
                                  ''', @Schema, ''', 
                                  ''', @Table, ''', 
                                  ''', @RuleWithQuotes, ''' 
                           FROM ', QUOTENAME(@Schema), '.', QUOTENAME(@Table), ' 
                           WHERE (', @ValidationRule, ')')
	  
EXEC sp_executesql @SqlStatement

With this approach, we can validate business rules across different tables in an automated and scalable way simply by entering the rules we want to validate into the configuration table, thereby detecting potential invalid data.

This solution could also be extended by using the invalid data table to trigger automated email alerts or to support a dashboard for monitoring recurring data quality issues; whilst those elements are outside the scope of this article, they illustrate how the framework can contribute to broader data quality management.

Validating Data with Dynamic SQL – DDL and Stored Procedure

CREATE TABLE [dbo].[Employees] (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    Age  DECIMAL(38,0),
    Mail NVARCHAR(50),
    Department NVARCHAR(50),
    HireDate DATE
);

--Data for Employees Table
INSERT INTO [dbo].[Employees] (Name, Age, Mail,Department, HireDate)
VALUES 
('John', 32, 'john@gmail.com' ,'Finance', '2018-03-15'),
(NULL, 28, 'unkown','Marketing', '2020-07-01'),
('Bob', 45, 'bob@gmail.com','IT', '2010-11-23'),
('Maria', 37, 'mariaagmail.com','HR', '2016-05-10'),
('Daniel', 30, 'daniel@gmail.com','Operations', '2022-01-05');

-- DDL for BUSINESS_RULES_VALIDATIONS table
CREATE TABLE [dbo].[BUSINESS_RULES_VALIDATIONS] (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    [SCHEMA] NVARCHAR(5),
    [TABLE] NVARCHAR(20),
    VALIDATION_RULE NVARCHAR(MAX)
);

-- Data for BUSINESS_RULES_VALIDATIONS table
INSERT INTO [dbo].[BUSINESS_RULES_VALIDATIONS] ([SCHEMA], [TABLE], VALIDATION_RULE)
VALUES
('dbo', 'Employees', 'Name IS NULL'),
('dbo', 'Employees', 'TRY_CAST(Age AS INT) IS NULL'),
('dbo', 'Employees', 'CHARINDEX(''@'', Mail) <= 1'); 

-- DDL for INVALID_DATA table 

CREATE TABLE [dbo].[INVALID_DATA] (
    ROW_ID INT NOT NULL,
    [SCHEMA] NVARCHAR(10) NOT NULL,
    [TABLE] NVARCHAR(20) NOT NULL, 
    VALIDATED_RULE NVARCHAR(MAX) NOT NULL
);

-- DDL for VALIDATE_DATA store procedure
CREATE OR ALTER PROCEDURE [dbo].[VALIDATE_DATA]
AS
   BEGIN

   DECLARE @Schema                  NVARCHAR(10), 
           @Table                   NVARCHAR(100),
           @RowCount                INT, 
           @SqlStatement            NVARCHAR(MAX), 
           @SqlStatementParameters  NVARCHAR(1000), 
           @ValidationRule          NVARCHAR(MAX),
           @RuleWithQuotes          NVARCHAR(MAX),
           @ValidationRules         CURSOR, 
           @IdentityColumn          NVARCHAR(100)

   SET @ValidationRules = CURSOR LOCAL STATIC READ_ONLY FOR 
         SELECT  [SCHEMA], 
                 [TABLE], 
                 [VALIDATION_RULE]
         FROM    [dbo].[BUSINESS_RULES_VALIDATIONS] 

   OPEN @ValidationRules 

   FETCH NEXT FROM @ValidationRules INTO @Schema, @Table, @ValidationRule 

   WHILE @@FETCH_STATUS = 0
      BEGIN
         SET @SqlStatementParameters = '@RowCountOutput INT OUTPUT' 
         SET @SqlStatement =  'SELECT @RowCountOutput = COUNT(*) FROM' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + @ValidationRule
	 PRINT @SqlStatement

         EXEC sp_executesql @SqlStatement, @SqlStatementParameters, @RowCountOutput = @RowCount OUTPUT

         PRINT @RowCount

         IF @RowCount > 0
            BEGIN

               SELECT      @IdentityColumn = COL.name 
               FROM        sys.all_columns COL
               INNER JOIN  sys.all_objects TBL
               ON          TBL.object_id = COL.object_id
               INNER JOIN  sys.schemas SCH
               ON          TBL.schema_id = SCH.schema_id
               AND         SCH.name = @Schema
               WHERE       TBL.name = @Table 
               AND         COL.is_identity = 1

               PRINT 'The identity column is: ' + @IdentityColumn

               SET @RuleWithQuotes = REPLACE(@ValidationRule, '''', '''''')

               SET @SqlStatement =  CONCAT('INSERT INTO [dbo].[INVALID_DATA] ([ROW_ID], [SCHEMA], [TABLE], [VALIDATED_RULE]) SELECT ', @IdentityColumn, ', ''', @Schema, ''', ''', @Table, ''', ''', @RuleWithQuotes, ''' FROM ', QUOTENAME(@Schema), '.', QUOTENAME(@Table), ' WHERE (', @ValidationRule, ')')
               PRINT @SqlStatement

               EXEC sp_executesql @SqlStatement
            END

         FETCH NEXT FROM @ValidationRules INTO @Schema, @Table, @ValidationRule
      END
   CLOSE @ValidationRules

   DEALLOCATE @ValidationRules
   END
GO

EXEC [dbo].[VALIDATE_DATA]

SELECT *
FROM [dbo].[INVALID_DATA]

Dynamic SQL Limitations

As with any technique, dynamic SQL offers benefits and drawbacks. Understanding those trade-offs upfront is important when deciding whether it is the right choice for a given solution.

SQL Injection

One of the biggest concerns when using dynamic SQL is SQL injection. These attacks happen when an input is manipulated in a way that allows unauthorised SQL commands to be executed. In practice, this can lead to deleted or altered data, unrestricted access to database objects, or the exposure of sensitive information. However, the risk is not limited to data modification: attackers may also use SQL injection to retrieve confidential information, such as usernames, passwords, financial records, or personal data, creating serious security and privacy issues for both the application and the underlying database.

It is essential to validate user input to ensure that it does not contain any malicious code or native SQL commands that could negatively impact the database. Filtering out certain words or characters can help mitigate the risk, but a more comprehensive approach is required to effectively guard against SQL injection attacks.

In the example described in this article, one possible control could be to verify that no commands such as UPDATE, DELETE, ALTER, DROP, SELECT, or semicolons are present in the configuration table before processing the validations. If any of these elements are detected, the validation will not be performed.

Permission Restrictions

Many organisations implement strict security policies that can limit the use of dynamic SQL, either partially or entirely. This is often tied to broader risk mitigation measures, such as protection against SQL injection.

Before adopting dynamic SQL to develop a solution, it is important to understand the security policies that apply to the database environment in question.

Dynamic Query Length Limits

The dynamic query that we define is stored in a variable that has a length limit. When using SQL Server, if the query is stored in a variable defined as NVARCHAR(MAX) or VARCHAR(MAX), long strings are not an issue as their size can reach up to 2 GB, subject to available server memory. Both EXEC and sp_executesql can execute queries of that size when using MAX types.

However, if the defined length limit is exceeded, the query string may be truncated or cause an error, depending on the database engine. This can make the query invalid or incomplete, leading to unexpected results. In such cases, one option is to reduce the query size or breaking it down into smaller segments: instead of executing one large query, we can run multiple smaller queries sequentially, achieving the same result.

Code maintenance cost

Dynamic SQL code is often more cumbersome to test and maintain, especially when queries are long and complex. Debugging and optimisation become more challenging as the queries are generated at runtime.

To mitigate this, the code should be structured as clearly as possible. In practice, this means keeping queries simple, applying consistent indentation, and documenting the logic thoroughly so the solution remains understandable over time.

Conclusion

Dynamic SQL is a very useful technique in situations where the query structure needs to adapt to input parameters, changing conditions, or specific scenarios. In business contexts where other technologies or programming languages are either unavailable or unsuitable, dynamic SQL offers an effective solution for automating routine and repetitive tasks, as demonstrated in this article’s use case example.

That said, its benefits need to be balanced against the associated risks and constraints. Security considerations, especially about SQL injection, must be addressed. It is also important to assess permission restrictions, query length considerations, and the maintenance required before implementing a dynamic SQL-based solution.

Nevertheless, in the right context, dynamic SQL can be a strong choice for developing technical solutions.

Are you interested in optimising routine database processes, strengthening data quality systems, or automating new ETL workflows? If so, the ClearPeaks team is here for you, so contact us today to find out how we can help you to achieve your data goals!