Stored Procedures
Overview
Stored procedures are pre‑compiled collections of SQL statements that perform a specific task. They help encapsulate business logic, improve performance, and reduce network traffic.
CREATE PROCEDURE [schema_name.]procedure_name
[ @parameter_name datatype [= default] [OUT | OUTPUT],
...n ]
AS
BEGIN
-- T‑SQL statements
END
GOExample: Simple Procedure
This procedure returns the total sales for a given year.
CREATE PROCEDURE dbo.GetAnnualSales
@Year INT
AS
BEGIN
SET NOCOUNT ON;
SELECT SUM(Amount) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = @Year;
END;
GOParameters
Parameters allow you to pass values into a stored procedure.
@param_name datatype– defines the parameter.- Optional default values:
@p INT = 0. - Use
OUTPUTto return data to the caller.
Best Practices
- Use schema‑qualified names.
- Set
SET NOCOUNT ON;to reduce extra result sets. - Avoid dynamic SQL when possible.
- Document each parameter and expected result set.