Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 39 ยท Procedures, Triggers and Functions

Dynamic SQL and Cursors in T-SQL

Build SQL strings safely with sp_executesql and QUOTENAME, avoid SQL injection, and learn when a cursor is justified and how to replace most cursors with set-based code.

Two T-SQL features that every SQL Server developer meets, and that are both double-edged: dynamic SQL, where you build a statement as text and run it, and cursors, where you process rows one at a time. Both are sometimes the right tool and often a sign that something could be simpler. This lesson shows how to use each safely and what to do instead.

Neither runs in the browser, so the T-SQL is read-only, with runnable stand-ins. For the portable background see dynamic SQL and SQL injection.

Dynamic SQL

You need dynamic SQL when something in the statement can't be a parameter: a table name, a column name, a variable list of columns or a flexible search with optional filters. Parameters work only for values.

EXEC (string) is dangerous

SQL
-- DON'T: concatenating user input
DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.Customers WHERE LastName = ''' + @LastName + N'''';
EXEC (@sql);

If @LastName is x'; DROP TABLE dbo.Customers; --, you have just handed the attacker your database. The query also gets a new plan for each distinct text.

sp_executesql with parameters

sp_executesql runs a string with real parameters, so values never become part of the code:

SQL
DECLARE @sql nvarchar(max) =
    N'SELECT CustomerId, FullName FROM dbo.Customers WHERE LastName = @LastName';

EXEC sp_executesql
     @sql,
     N'@LastName nvarchar(50)',
     @LastName = @InputLastName;

Three arguments: the statement (always nvarchar), the parameter definitions, then the values. The plan is cached and reused for every value, and injection through the value is impossible.

QUOTENAME for identifiers

When a table or column name is dynamic, you can't parameterize it, so escape it with QUOTENAME, which wraps it in brackets and doubles any closing bracket:

SQL
DECLARE @table sysname = N'Orders; DROP TABLE x';
SELECT QUOTENAME(@table);          -- [Orders; DROP TABLE x]

DECLARE @sql nvarchar(max) =
    N'SELECT COUNT(*) FROM dbo.' + QUOTENAME(@table);

Even better, validate the name against a whitelist, such as sys.tables, before using it:

SQL
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @table AND schema_id = SCHEMA_ID('dbo'))
    THROW 50010, 'Unknown table', 1;

A common pattern builds the WHERE clause only from filters the caller supplied, while keeping every value a parameter:

SQL
DECLARE @sql nvarchar(max) = N'SELECT OrderId, Total FROM dbo.Orders WHERE 1 = 1';

IF @CustomerId IS NOT NULL SET @sql += N' AND CustomerId = @CustomerId';
IF @MinTotal   IS NOT NULL SET @sql += N' AND Total >= @MinTotal';

EXEC sp_executesql @sql,
     N'@CustomerId int, @MinTotal money',
     @CustomerId, @MinTotal;

You can see the idea in SQLite by building the same statement text with a string, then reading it back. Notice that the text carries placeholders, not the values:

SQL Building a statement without values in it

Press Run (or Ctrl + Enter) to see the result here.

The generated text mentions only parameter names. In SQL Server sp_executesql binds the values separately. The same filters written as one static query use (:customer_id IS NULL OR customer_id = :customer_id):

SQL The static 'catch-all' version

Press Run (or Ctrl + Enter) to see the result here.

It's simpler, but on big tables one plan can't suit every combination, which is why dynamic SQL (or OPTION (RECOMPILE)) is used for search screens.

Dynamic SQL runs in its own scope: local variables of the caller aren't visible, and a #temp table created inside disappears afterwards. Permissions are checked against the caller, not the procedure owner, so ownership chaining is broken.

Cursors

A cursor walks through a result set one row at a time:

SQL
DECLARE @Id int, @Name nvarchar(100);

DECLARE product_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT ProductId, Name FROM dbo.Products WHERE Discontinued = 1;

OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @Id, @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.ArchiveProduct @ProductId = @Id;
    FETCH NEXT FROM product_cursor INTO @Id, @Name;
END;

CLOSE product_cursor;
DEALLOCATE product_cursor;

LOCAL FAST_FORWARD (forward-only, read-only) is the cheapest kind. Cursors are slow because every row costs a round of overhead, and they hold locks longer. The right question is "can one statement do this?" Usually yes:

Cursor task Set-based replacement
Update each row with a calculation One UPDATE with an expression or join
Running totals SUM() OVER (ORDER BY ...)
Row numbering, ranking ROW_NUMBER(), RANK()
Compare with the previous row LAG(), LEAD()
Build a comma list STRING_AGG
Call a procedure per row Rewrite the procedure to take a table-valued parameter

A running total is a good example. A cursor adds as it goes; a window function does it in one pass:

SQL Running total without a cursor

Press Run (or Ctrl + Enter) to see the result here.

Legitimate cursor uses are rare: administrative loops (run a command per database or per table), calling a procedure that can't be set-based, or one-off scripts where clarity beats speed.

flowchart TD
  A["Need to process many rows?"] --> B{"Can one statement do it?"}
  B -- "yes" --> C["Set-based UPDATE / window function"]
  B -- "no" --> D{"Dynamic names or columns?"}
  D -- "yes" --> E["sp_executesql + QUOTENAME"]
  D -- "no" --> F["LOCAL FAST_FORWARD cursor, last resort"]

Try the options from the top; the cursor is the last stop.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does sp_executesql protect against SQL injection better than EXEC (@string)?

    Parameters are bound separately, so a value can never change the structure of the statement.
  2. 2. What does QUOTENAME(N'My Table') return?

    QUOTENAME wraps the identifier in square brackets and doubles any closing bracket inside it.
  3. 3. Which cursor options are the cheapest for read-only processing?

    A forward-only, read-only local cursor carries the least overhead.
  4. 4. Which T-SQL feature replaces a cursor that computes a running total?

    Window functions compute running totals in a single set-based pass.

Summary

  • Use dynamic SQL only when names or structure vary; pass values through sp_executesql parameters and escape identifiers with QUOTENAME plus a whitelist.
  • Never concatenate user values into SQL text.
  • Cursors process row by row and are slow; try set-based statements and window functions first.
  • When you must use a cursor, make it LOCAL FAST_FORWARD.

Next: Clustered and nonclustered indexes