Lesson 1

Many business applications execute the same database operations repeatedly: insert a new order, validate a customer, calculate totals, update inventory, or generate a daily report. In SQL Server, a stored procedure lets you package that repeatable work into a named, reusable unit that runs close to the data.

In this module, you will learn how stored procedures support consistent transaction processing, enforce business rules, and simplify application development by centralizing database logic in one place.

After completing this module, you will be able to:

1. Describe what a stored procedure is and when to use one.
2. Implement business rules in database logic using stored procedures.
3. Create, modify, and delete stored procedures in SQL Server.
4. Use input and output parameters to pass values in and out of procedures.
5. Test stored procedures for correctness and predictable behavior.

Stored procedures are widely used in production SQL Server environments for two practical reasons:

1. Consistency and maintainability: A stored procedure centralizes the steps for a transaction (for example, “create invoice” or “apply payment”). Instead of copying the same SQL into multiple applications or scripts, you maintain one authoritative implementation. When business rules change, you update the procedure once and all callers benefit from the update.
2. Security and controlled access: Stored procedures allow you to grant permissions at the procedure level so users or application roles can perform approved operations without being granted broad direct access to underlying tables. This is a common pattern for enforcing least privilege and reducing accidental or malicious changes.

Performance can also improve in many scenarios because SQL Server can reuse cached execution plans for frequently executed procedures, reducing repeated compilation overhead. However, performance depends on query design, indexing, and the specific workload.

Some SQL Server workloads use natively compiled stored procedures (In-Memory OLTP) for specific performance-sensitive scenarios. The example below demonstrates a procedure definition followed by an ALTER PROCEDURE that changes the SELECT list.

CREATE PROCEDURE [dbo].[usp_1]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
  TRANSACTION ISOLATION LEVEL = SNAPSHOT,
  LANGUAGE = N'us_english'
)
  SELECT c1, c2 FROM dbo.T1;
END
GO

ALTER PROCEDURE [dbo].[usp_1]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
  TRANSACTION ISOLATION LEVEL = SNAPSHOT,
  LANGUAGE = N'us_english'
)
  SELECT c1 FROM dbo.T1;
END
GO

In practice, you use ALTER PROCEDURE to modify procedure logic while preserving object identity, permissions, and dependencies. If you need SQL Server to regenerate a procedure’s plan on the next execution (for example, after significant data distribution changes), you can use sp_recompile to mark it for recompilation.
In the next lesson, you will define stored procedures more formally and learn how they are used in everyday SQL Server development.