Databases · DBC-57

SQL Server Stored Procedures and T-SQL Programming

SQL Server Stored Procedures and T-SQL Programming is a course in writing server-side code with T-SQL on SQL Server 2025, covering stored procedures, functions, triggers, TRY...CATCH, transactions, safe dynamic SQL and parameter sniffing. It suits developers and database administrators who already write queries and want database logic that is reliable, secure and fast.

Updated 30 Oct 2026

From 6,210 THB / person 6,900 −10% excl. VAT 7% · group rates available

Corporate training quote Join the waiting list

PDFDownload the course outline

Duration12 hours · 2 days
FormatOnsite / live online
Next roundOn request
CertificateIncluded

Course overview

Many business systems keep critical logic in SQL Server: saving orders, deducting stock, calculating commissions and running reports that applications call every day. Stored procedures written without standards often ignore errors and leave data half-written when something fails, open the door to SQL injection through dynamic SQL, crawl through rows one at a time with cursors, or run fast one day and unusually slowly the next without anyone knowing why.

This course teaches systematic T-SQL programming on SQL Server 2025. It starts with variables and control flow, stored procedures with input, output and table-valued parameters, scalar and inline table-valued functions, choosing temp tables or table variables, turning cursor-based work into set-based code and using triggers with care. It then focuses on correctness with TRY...CATCH, THROW, transactions and isolation levels, writing dynamic SQL safely, managing permissions with EXECUTE AS, and understanding execution plans and parameter sniffing, and closes with a workshop that builds the data access layer of an ordering system with stored procedures. (2 days, 6 hours per day, 12 hours in total, Intermediate level.)

What you’ll gain

Write correct T-SQL scripts with variables, conditions and loops
Build stored procedures with input, output and table-valued parameters
Choose and write scalar and table-valued functions that fit the task
Turn cursor-based work into set-based code and choose temp tables or table variables
Handle errors with TRY...CATCH and THROW together with transactions
Understand isolation levels, blocking and deadlocks under concurrent use
Write dynamic SQL that is safe from SQL injection and set procedure permissions
Read execution plans and deal with parameter sniffing

Who this course is for

Application developers who write stored procedures or maintain existing database code
Learners who have finished SQL Server Database Queries and want to program inside the database
DBAs who review and improve the development team's T-SQL code
Analysts and data engineers who process data in SQL Server
Administrators of business systems with critical logic in SQL Server

Prerequisites

Fluent T-SQL queries such as SELECT, JOIN, GROUP BY, subqueries and CTEs
INSERT, UPDATE and DELETE, and an understanding of primary and foreign keys
Able to use SQL Server Management Studio or an equivalent tool
A laptop that can install SQL Server 2025 Developer Edition, with administrator rights

Curriculum

Course Details

This course builds on Microsoft SQL Server 2025 Database Queries and goes deep into programming inside the database. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs on a single sample ordering and stock database. Intermediate level. Every lab uses SQL Server 2025 Developer Edition, free on the learner's own machine, and most principles also apply to SQL Server 2019 and 2022. The course focuses on database code that is correct, secure and fast. It does not cover server administration, backups or in-depth index tuning, which are taught in DBA Microsoft SQL Server 2025 and SQL Server Performance Tuning and Optimization.

Day 1 T-SQL Programming and Stored Procedures

Section 1: Lab: T-SQL Programming Basics

Set up SQL Server 2025 Developer Edition, SSMS and the sample database
Batches, GO, variables and variable scope
IF ... ELSE, WHILE, BEGIN ... END and CASE
Functions new in SQL Server 2025 such as the || concatenation operator, CURRENT_DATE and REGEXP_LIKE
Lab: a script that checks and fixes customer data with variables and conditions

Section 2: Lab: Stored Procedures and Parameters

CREATE OR ALTER PROCEDURE and naming conventions
Input parameters, default values and OUTPUT parameters
Return codes versus result sets, and SET NOCOUNT ON
Table-valued parameters and accepting JSON with OPENJSON
Lab: a procedure that saves an order header and lines in one call and returns the document number

Section 3: Lab: User-defined Functions

Scalar functions and their performance impact, including scalar UDF inlining
Inline table-valued functions versus multi-statement table-valued functions
Using functions with CROSS APPLY and OUTER APPLY
Choosing between a function, a view and a stored procedure
Lab: a net price function and a sales-by-date-range table-valued function

Section 4: Lab: Temp Tables, Table Variables and Set-based Code

Temp tables versus table variables: scope, statistics and plan impact
How cursors work and why they are usually slower than set-based code
Replace cursors with joins, window functions and MERGE
When row-by-row processing is still needed, and how to do it safely
Lab: rewrite a cursor-based commission calculation as set-based code and time it

Section 5: Lab: Triggers

AFTER triggers and INSTEAD OF triggers
The inserted and deleted tables, and writing triggers that handle many rows
Recording an audit log of data changes with triggers
The downside of triggers hiding logic, and alternatives such as temporal tables
Lab: a trigger that keeps a history of product price changes

Day 2 Correctness, Security and Performance

Section 6: Lab: Error Handling with TRY...CATCH

TRY...CATCH with ERROR_NUMBER, ERROR_MESSAGE and ERROR_LINE
THROW versus RAISERROR, and defining your own error messages
A central error log table shared by every procedure
XACT_STATE and dealing with failed transactions in CATCH
Lab: add error handling to the order procedure

Section 7: Lab: Transactions and Isolation Levels

BEGIN TRAN, COMMIT, ROLLBACK and SET XACT_ABORT ON
How nested transactions and savepoints really behave
Isolation levels: Read Committed, Read Committed Snapshot, Snapshot and Serializable
Blocking, deadlocks and an overview of optimized locking in SQL Server 2025
Lab: deduct stock for many concurrent users without overselling

Section 8: Lab: Dynamic SQL and Procedure Security

How SQL injection happens inside procedures
sp_executesql with parameters, and QUOTENAME for object names
Catch-all queries: OPTION (RECOMPILE) versus dynamic SQL
Grant EXECUTE instead of table permissions, ownership chaining and EXECUTE AS
Keep personal data out of logs beyond what is needed, in line with PDPA
Lab: a safe multi-criteria product search, with an app user that cannot touch tables directly

Section 9: Lab: Execution Plans and Parameter Sniffing

The plan cache and how stored procedure plans are reused
Read execution plans and SET STATISTICS IO, TIME to find slow spots
What parameter sniffing is and how it shows up in real systems
An overview of parameter sensitive plan optimization and optional parameter plan optimization in SQL Server 2025
Use Query Store to see performance history and force a good plan
Lab: reproduce parameter sniffing and compare the fixes

Section 10: Workshop: A Data Access Layer with Stored Procedures

Brief: a small ordering and stock system
Design a set of procedures, functions and triggers to one standard
Add complete error handling, transactions and permissions
Test with scripts, and use an AI assistant to review code while checking the results yourself
Present and review the code together

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

Tailor the content to your team’s tools and projects
Your dates, at your office or live online
Quotation with tax ID for procurement

Corporate training quote

Instructors

Sanitwong KamonpakornInstructor

Frequently asked questions

How does this course differ from Microsoft SQL Server 2025 Database Queries?

This course builds on Microsoft SQL Server 2025 Database Queries, which focuses on writing queries from SELECT to window functions and JSON and only touches on programming and transactions. This course goes deep into database code: stored procedures, functions, triggers, error handling, transactions and isolation levels, safe dynamic SQL, procedure permissions and parameter sniffing.

Do I have to use SQL Server 2025?

The labs use SQL Server 2025 Developer Edition, which is free for development and testing, but most principles also apply to SQL Server 2019 and 2022. The instructor points out which features are specific to SQL Server 2025, such as the regular expression functions, optimized locking and optional parameter plan optimization.

Can I use a MacBook?

Yes, by running SQL Server 2025 in Docker and using VS Code with the MSSQL extension instead of SSMS, which covers almost every lab. Some topics, such as full graphical execution plans, are easier in SSMS on Windows. Test your installation before the training day.

Should all business logic move into stored procedures?

Not necessarily. The course teaches criteria for deciding which logic suits the database, such as work that needs data integrity or processes large volumes of data, and which belongs in the application, so your team can choose what fits its own system.

Can I bring my organisation's code or database to class?

You can bring code samples to discuss during the workshop and Q&A, but remove confidential and personal data and do not bring real databases. All main labs use a sample database that can be recreated from a script.

Which course should I take next?

To go deeper into index and server performance tuning, continue with SQL Server Performance Tuning and Optimization. To manage servers, backups and availability, continue with DBA Microsoft SQL Server 2025.

Articles are published in Thai.