Final Chapter: Master SQL Server Stored Procedures – Batch Execution & Params

What is a stored procedure?

A stored procedure is a database‑level method or function that packages complex SQL statements, conditional logic, batch operations, and transaction handling under a single name, allowing one‑line execution without rewriting large code blocks.

Core advantages

Stored procedures execute quickly, support parameters, are reusable, can be scheduled, and provide strong confidentiality because the underlying table schema is hidden from callers.

Typical usage scenarios

Batch insert, update, or clean‑up of data.

Encapsulating multi‑step business processes such as order placement, settlement, and reconciliation.

Daily scheduled reports, data archiving, and data synchronization.

Ensuring consistent business logic across developers.

Abstracting table structures for safer application calls.

Stored procedure vs. single SQL vs. view

• Single SQL: ad‑hoc execution with poor reusability.

• View: only encapsulates a query; cannot contain logic or accept parameters.

• Stored procedure: supports queries, DML, conditional statements, loops, parameters, and transactions – the all‑round champion.

Example 1 – Simple parameter‑less procedure

Requirement: encapsulate a common query that returns all users and their related orders.

-- 创建无参存储过程

CREATE PROCEDURE usp_GetUserOrderList

AS

BEGIN

SELECT u.UserID, u.UserName, u.Age, o.OrderPrice, o.OrderTime

FROM UserInfo u

LEFT JOIN OrderInfo o ON u.UserID = o.UserID;

END

GO

Invocation (one‑click execution):

EXEC usp_GetUserOrderList;

GO

Example 2 – Procedure with input parameter

Requirement: pass a user ID to retrieve that user's order information.

-- 创建带参存储过程

CREATE PROCEDURE usp_GetUserOrderByID

@UserID INT -- 输入参数：用户ID

AS

BEGIN

SELECT * FROM OrderInfo WHERE UserID = @UserID;

END

GO

Calling with a specific ID:

EXEC usp_GetUserOrderByID @UserID = 2;

GO

Example 3 – Procedure with default‑value parameter

Requirement: query users by age, defaulting to 25 when no argument is supplied.

CREATE PROCEDURE usp_GetUserByAge

@Age INT = 25 -- 默认参数，不传默认查25岁

AS

BEGIN

SELECT * FROM UserInfo WHERE Age = @Age;

END

GO

Calling without a parameter (uses default): EXEC usp_GetUserByAge; Calling with an explicit age:

EXEC usp_GetUserByAge @Age = 28;

Example 4 – Modifying and dropping a procedure

Altering an existing procedure:

-- 修改存储过程

ALTER PROCEDURE usp_GetUserOrderList

AS

BEGIN

SELECT * FROM UserInfo;

END

GO

Dropping the procedure when it is no longer needed:

-- 删除存储过程

DROP PROCEDURE IF EXISTS usp_GetUserOrderList;

GO

Best‑practice checklist for beginners

Naming convention: prefix procedures with usp_ to distinguish them from views and tables.

Parameters must start with @ and match the data type of the corresponding column.

Wrap complex business logic in a transaction to ensure data safety.

Avoid redundant logic inside procedures to simplify future maintenance.

Stored procedures are pre‑compiled; repeated calls are faster than raw SQL.

Series recap

The 15‑article series covers the full pipeline from databases, tables, CRUD, conditions, sorting, aggregation, joins, sub‑queries, indexes, transactions, views, and finally stored procedures, providing a comprehensive foundation for daily development, data analysis, and interview preparation.