Dynamic SQL and sp_executesql

Separate values from code.

UnderstandStudy an examplePractise

Understand the concept

Dynamic SQL changes query structure at runtime. Pass user values through sp_executesql parameters instead of concatenating them into SQL text. Parameters cannot stand in for table or column identifiers; those require a fixed allowlist. QUOTENAME delimits an identifier, but does not authorise access.

Parameterisation can encourage plan reuse; plan quality still depends on data distribution. The N prefix preserves Unicode. Use nvarchar(max) for statement text and match parameter types to columns. Calling sp_executesql does not repair text that has already been concatenated unsafely. Reject structural choices outside the allowlist.

WHEN TO USE IT

Use dynamic filters and allowlisted ordering.

COMMON PITFALL

Concatenating values and then assuming sp_executesql makes them safe.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

EXEC sys.sp_executesql N'SELECT ProductId, ProductName FROM lab.Product;';

Executes fixed SQL text.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Write a dynamic query with a minimum-price parameter.

Show the solution and explanation

Worked solution

EXEC sys.sp_executesql N'SELECT ProductName, Price FROM lab.Product WHERE Price >= @Min;', N'@Min decimal(10,2)', @Min = 1000;

The price value stays separate from statement text.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.