Dynamic SQL Done Safely: sp_executesql, Parameters and QUOTENAME

Dynamic SQL, building a query as a string and executing it, is sometimes the right tool: flexible search screens, choosing a sort column at run time, or maintenance scripts that loop over tables. It is also the classic route to SQL injection. The rules for doing it safely are short and strict.

Safe dynamic SQL in SQL Server with sp_executesql

The dangerous way

-- DO NOT DO THIS
DECLARE @sql NVARCHAR(MAX) =
    N'SELECT * FROM dbo.Employee WHERE FullName = ''' + @Name + N'''';
EXEC (@sql);

If @Name contains x'; DROP TABLE dbo.Employee; --, it becomes part of the SQL and runs. Concatenating values also produces a different query text for every value, so SQL Server compiles a new plan each time and fills the plan cache.

Rule 1: pass values as parameters with sp_executesql

DECLARE @sql NVARCHAR(MAX) =
    N'SELECT EmployeeId, FullName, DepartmentId
      FROM dbo.Employee
      WHERE FullName = @Name AND DepartmentId = @DeptId;';

EXEC sys.sp_executesql
    @sql,
    N'@Name NVARCHAR(100), @DeptId INT',
    @Name = @Name, @DeptId = @DeptId;

The values never become part of the SQL text, so they cannot change its meaning, and the query text stays the same, so its plan is reused.

Rule 2: object names go through QUOTENAME and a whitelist

Table and column names cannot be parameters. When they must vary, validate them against a list you control, then wrap them with QUOTENAME:

DECLARE @SortColumn SYSNAME = N'HireDate',
        @SortDir    NVARCHAR(4) = N'DESC';

IF @SortColumn NOT IN (N'FullName', N'HireDate', N'DepartmentId')
    THROW 50010, 'Invalid sort column.', 1;
IF @SortDir NOT IN (N'ASC', N'DESC')
    THROW 50011, 'Invalid sort direction.', 1;

DECLARE @sql NVARCHAR(MAX) =
    N'SELECT EmployeeId, FullName, HireDate, DepartmentId
      FROM dbo.Employee
      ORDER BY ' + QUOTENAME(@SortColumn) + N' ' + @SortDir + N';';

EXEC sys.sp_executesql @sql;

QUOTENAME adds square brackets and escapes any closing bracket inside the name, so even an odd name cannot break out of the identifier.

Building optional search filters

A search screen with many optional filters is the best use of dynamic SQL. Add only the conditions the user supplied, always with parameters:

DECLARE @sql NVARCHAR(MAX) = N'SELECT EmployeeId, FullName, DepartmentId, HireDate
FROM dbo.Employee
WHERE 1 = 1';

IF @Name   IS NOT NULL SET @sql += N' AND FullName LIKE @Name + N''%''';
IF @DeptId IS NOT NULL SET @sql += N' AND DepartmentId = @DeptId';
IF @From   IS NOT NULL SET @sql += N' AND HireDate >= @From';

EXEC sys.sp_executesql @sql,
    N'@Name NVARCHAR(100), @DeptId INT, @From DATE',
    @Name = @Name, @DeptId = @DeptId, @From = @From;

Each combination of filters gets its own cached plan tailored to the columns actually used, which often beats a single "catch-all" query full of (@x IS NULL OR Column = @x) conditions.

Permissions

Code inside dynamic SQL runs with the permissions of the caller, not the owner of the stored procedure. A user allowed to execute a procedure may still get "permission denied" on the tables it queries dynamically. Grant the needed permissions explicitly, or sign the procedure with a certificate, rather than giving broad rights.

Debugging tips

  • Use NVARCHAR(MAX) and N'' literals to avoid silent truncation and Unicode issues.
  • PRINT @sql; (or SELECT @sql for long strings) before executing, to see exactly what will run.
  • Test with values containing quotes, brackets and semicolons.

Quick checklist

  • Values: always parameters via sp_executesql, never concatenated.
  • Names: whitelist plus QUOTENAME.
  • Use dynamic SQL for optional filters and sorting, not by default.
  • Check permissions and test with hostile input.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

Azure SQL Database vs SQL Server on Azure VM: Cost Comparison (2026)

Image
If you are moving a SQL Server workload to Azure, the first big decision is where it will run: Azure SQL Database (a fully managed database service) or SQL Server on an Azure Virtual Machine (your own SQL Server, running on a VM you control). Both work well. But they bill you in very different ways, and picking the wrong one can quietly double your monthly bill. This guide breaks down what you actually pay for in each option, where the hidden costs are, and a simple way to decide. Quick answer Choose Azure SQL Database for new applications, single databases, variable or spiky workloads, and teams that do not want to manage patching, backups or high availability. Choose SQL Server on an Azure VM when you need full instance control: SQL Agent jobs across databases, SSIS/SSRS on the same box, OS-level access, specific SQL Server versions, or third-party software installed next to SQL Server. Consider Azure SQL Managed Instance when you want near-full SQL Server compat...
Read more