A SQL Server stored procedure groups one or more Transact-SQL statements into a logical unit and is stored as an object in the Database Server. When a stored procedure is called at the first time, SQL Server creates an execution plan and stores it in the plan cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance.
This tutorial series introduces you to the stored procedures and shows you how to develop flexible stored procedures to optimize your database access.
Section 1. Getting started with SQL Server Stored Procedures
- A basic guide to stored procedures – show you how to create, execute, modify, and drop stored procedure in SQL Server.
- Parameters – learn how to create stored procedures with parameters. It also covers the optional parameters.
- Variables – introduce you to Transact-SQL variables and how to manipulate variables in stored procedures.
- Output Parameters – pass data back from a stored procedure back to the calling program using the output parameters.
Section 2. Control-of-flow statements
- BEGIN…END – create a statement block that consists of multiple Transact-SQL statements which execute together.
- IF ELSE – execute a statement block based on a condition.
- WHILE – repeatedly execute a set of statements based on a condition.
- BREAK – exit the loop immediately.
- CONTINUE – skip the current iteration of the loop immediately and continue the next one.
Section 3. Cursors
- Cursor – introduction to the cursor in SQL Server
Section 4. Handling Exceptions
- TRY CATCH – handle exceptions gracefully in stored procedures.
- RAISERROR – generate user-defined error messages and return it back to the application using the same format as the system error.
- THROW – raise an exception and transfer the execution to the CATCH block of a TRY CATCH construct.
Section 5. Dynamic SQL
- Dynamic SQL – learn how to construct general purpose and flexible SQL statements using the dynamic SQL technique.