Dynamic SQL — Complete Guide
Dynamic SQL — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of SQL Server Tutorial on Toolliyo Academy.
On this page
SQL Server Tutorial · Lesson 42 of 100
Dynamic SQL
SQL basics ✓ → Queries → Advanced
Queries · 2 — JOINs · ~6 min · SQL — Stored Procedures & Functions
What is this?
Dynamic SQL builds a statement string and runs it with sp_executesql. Use it when object names or shapes must vary — carefully.
Why should you care?
Admin tools may query a table name chosen at runtime. Dynamic SQL is powerful and dangerous if concatenated with user input.
See it live — copy this example
Run in SQL Server Management Studio (SSMS) or Azure Data Studio.
USE DataVerse;
DECLARE @City NVARCHAR(50) = N'Mumbai';
DECLARE @sql NVARCHAR(MAX) = N'
SELECT OrderId, Amount
FROM dbo.Orders
WHERE City = @pCity
ORDER BY OrderId DESC;';
EXEC sys.sp_executesql
@sql,
N'@pCity NVARCHAR(50)',
@pCity = @City;What happened?
- The filter value is passed as a typed parameter to sp_executesql — not pasted into the string.
- That blocks classic injection on @City.
Practice next
- Run the parameterized dynamic example.
- Change @City to Pune.
- Never concatenate @City into @sql with +.
- Add a second parameter @MinAmount.
- Print @sql before exec while learning.
Remember
Prefer static SQL first. Use sp_executesql with parameters. Whitelist dynamic identifiers.
Safe optional filters
DataVerse search builds optional clauses with parameters.
Outcome: Flexible filters without injection holes.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
