Stored Procedure SQL


SQL Server Stored Procedures

SQL Server stored procedures are used to group one or more Transact-SQL statements into logical units. The stored procedure are stored as named objects in the SQL Server Database Server.

When you call a stored procedure for the first time, SQL Server creates an execution plan and stores it in the 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 introduce you to the stored procedures and shows you how to develop flexible stored procedures to optimize the 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 a stored procedure in SQL Server.
  • Parameters – learn how to create stored procedures with parameters, including optional parameters.
  • Variables  –  introduce you to Transact-SQL variables and how to manipulate variables in stored procedures.
  • Output Parameters  – guide you on how to return data 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 that execute together.
  • IF ELSE – execute a statement block based on a condition.
  • WHILE – repeatedly execute a set of statements based on a condition as long as the condition is true.
  • BREAK – exit the loop immediately and skip the rest of the code after it within a loop.
  • CONTINUE – skip the current iteration of the loop immediately and continue the next one.

Section 3. Cursors

  • Cursor  – show you how to handle cursors.

Section 4. Handling Exceptions

  • TRY CATCH – learn how to handle exceptions gracefully in stored procedures.
  • RAISERROR – show you how to generate user-defined error messages and return it back to the application using the same format as the system error.
  • THROW – walk you through the steps of raising an exception and transferring 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.