2. 首页 > SQLServer教程 > 正文

SQLServer教程FG017-SQLServer存储过程开发实战

教程整理：风哥教程  |  更新时间：2026-01-31  |  教程分类：SQLServer教程  |  文档学习：2

目录大纲

内容简介

本文档基于SQLServer官方文档的存储过程内容，结合生产环境实际情况，详细讲解SQLServer存储过程的创建、参数传递、错误处理、性能优化等内容。风哥教程参考SQLServer官方文档Stored Procedures、T-SQL Programming等相关章节。

Part01-基础概念与理论知识

1.1 SQLServer存储过程概念

SQLServer存储过程是一组预编译的T-SQL语句，存储在数据库中供重复使用：

  • 预编译执行，提高性能
  • 减少网络流量
  • 增强安全性
  • 代码重用
  • 模块化设计

更多视频教程www.fgedu.net.cn

1.2 SQLServer存储过程优势

存储过程的主要优势：

  • 性能优势：预编译执行计划，减少编译开销
  • 安全优势：通过权限控制数据访问
  • 维护优势：集中管理业务逻辑
  • 网络优势：减少客户端与服务器之间的数据传输

学习交流加群风哥微信: itpux-com

1.3 SQLServer存储过程类型

SQLServer存储过程类型：

  • 系统存储过程：以sp_开头，存储在master数据库
  • 用户自定义存储过程：用户创建的存储过程
  • 扩展存储过程：以xp_开头，调用外部DLL
  • CLR存储过程：使用.NET编写的存储过程

学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 SQLServer存储过程设计原则

存储过程设计原则：

  • 单一职责原则：每个存储过程只做一件事
  • 参数验证：在存储过程开始处验证参数
  • 错误处理：使用TRY-CATCH处理错误
  • 事务管理：合理使用事务确保数据一致性
  • 注释规范：添加清晰的注释说明

风哥提示：存储过程设计应考虑可维护性和性能

2.2 SQLServer存储过程命名规范

存储过程命名规范：

— 命名规范示例
— 格式：[schema].[sp_][动作][对象]
— 例如：
fgedu.sp_insert_order — 插入订单
fgedu.sp_update_customer — 更新客户
fgedu.sp_delete_product — 删除产品
fgedu.sp_get_user_info — 获取用户信息
fgedu.sp_list_orders — 列出订单

更多学习教程公众号风哥教程itpux_com

2.3 SQLServer存储过程性能优化

存储过程性能优化建议：

  • 使用SET NOCOUNT ON减少网络流量
  • 避免在WHERE子句中使用函数
  • 使用参数嗅探优化执行计划
  • 避免使用临时表，优先使用表变量
  • 合理使用索引

from SQLServer视频:www.itpux.com

Part03-生产环境项目实施方案

3.1 SQLServer基础存储过程

创建基础存储过程：

— 创建测试表
USE fgedudb;
GO
CREATE TABLE fgedu.products (
product_id INT IDENTITY(1,1) PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2),
stock INT DEFAULT 0,
create_time DATETIME DEFAULT GETDATE()
);
GO

INSERT INTO fgedu.products (product_name, category, price, stock)
VALUES
(‘笔记本电脑’, ‘电子产品’, 5999.00, 100),
(‘智能手机’, ‘电子产品’, 3999.00, 200),
(‘机械键盘’, ‘电脑配件’, 399.00, 500),
(‘无线鼠标’, ‘电脑配件’, 99.00, 800);
GO

— 创建基础查询存储过程
CREATE PROCEDURE fgedu.sp_get_all_products
AS
BEGIN
SET NOCOUNT ON;

SELECT
product_id,
product_name,
category,
price,
stock
FROM fgedu.products
ORDER BY product_id;
END;
GO

— 执行存储过程
EXEC fgedu.sp_get_all_products;
GO

执行结果：

(4 rows affected)

product_id product_name category price stock
———– ————- ———- ——– —–
1 笔记本电脑 电子产品 5999.00 100
2 智能手机 电子产品 3999.00 200
3 机械键盘 电脑配件 399.00 500
4 无线鼠标 电脑配件 99.00 800

3.2 SQLServer参数化存储过程

创建参数化存储过程：

— 创建带输入参数的存储过程
CREATE PROCEDURE fgedu.sp_get_products_by_category
@category VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;

SELECT
product_id,
product_name,
category,
price,
stock
FROM fgedu.products
WHERE category = @category
ORDER BY price DESC;
END;
GO

— 执行带参数的存储过程
EXEC fgedu.sp_get_products_by_category @category = ‘电子产品’;
GO

— 创建带输出参数的存储过程
CREATE PROCEDURE fgedu.sp_get_product_count
@category VARCHAR(50) = NULL,
@total_count INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

IF @category IS NULL
SELECT @total_count = COUNT(*) FROM fgedu.products;
ELSE
SELECT @total_count = COUNT(*) FROM fgedu.products WHERE category = @category;
END;
GO

— 执行带输出参数的存储过程
DECLARE @count INT;
EXEC fgedu.sp_get_product_count @category = ‘电子产品’, @total_count = @count OUTPUT;
PRINT ‘电子产品数量: ‘ + CAST(@count AS VARCHAR(10));
GO

— 执行不带参数的存储过程
DECLARE @count INT;
EXEC fgedu.sp_get_product_count @total_count = @count OUTPUT;
PRINT ‘总产品数量: ‘ + CAST(@count AS VARCHAR(10));
GO

执行结果：

product_id product_name category price stock
———– ————- ———- ——– —–
1 笔记本电脑 电子产品 5999.00 100
2 智能手机 电子产品 3999.00 200

电子产品数量: 2

总产品数量: 4

3.3 SQLServer高级存储过程

创建高级存储过程：

— 创建带错误处理的存储过程
CREATE PROCEDURE fgedu.sp_insert_product
@product_name VARCHAR(100),
@category VARCHAR(50),
@price DECIMAL(10,2),
@stock INT = 0,
@product_id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY
— 参数验证
IF @product_name IS NULL OR LEN(@product_name) = 0
RAISERROR(‘产品名称不能为空’, 16, 1);

IF @price < 0 RAISERROR('价格不能为负数', 16, 1); IF @stock < 0 RAISERROR('库存不能为负数', 16, 1); -- 插入数据 INSERT INTO fgedu.products (product_name, category, price, stock) VALUES (@product_name, @category, @price, @stock); SET @product_id = SCOPE_IDENTITY(); PRINT '产品添加成功，ID: ' + CAST(@product_id AS VARCHAR(10)); END TRY BEGIN CATCH PRINT '错误: ' + ERROR_MESSAGE(); SET @product_id = 0; END CATCH; END; GO -- 执行存储过程 DECLARE @new_id INT; EXEC fgedu.sp_insert_product @product_name = '显示器', @category = '电脑配件', @price = 1299.00, @stock = 150, @product_id = @new_id OUTPUT; GO -- 测试错误处理 DECLARE @new_id INT; EXEC fgedu.sp_insert_product @product_name = '', @category = '电脑配件', @price = -100, @stock = 50, @product_id = @new_id OUTPUT; GO

执行结果：

产品添加成功，ID: 5

错误: 产品名称不能为空

Part04-生产案例与实战讲解

4.1 SQLServer订单处理存储过程

订单处理存储过程实战：

— 创建订单相关表
CREATE TABLE fgedu.orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME DEFAULT GETDATE(),
total_amount DECIMAL(15,2),
status VARCHAR(20) DEFAULT ‘PENDING’
);
GO

CREATE TABLE fgedu.order_items (
item_id INT IDENTITY(1,1) PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES fgedu.orders(order_id)
);
GO

— 创建订单处理存储过程
CREATE PROCEDURE fgedu.sp_create_order
@customer_id INT,
@product_list VARCHAR(MAX), — 格式: product_id:quantity,product_id:quantity
@order_id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @total_amount DECIMAL(15,2) = 0;
DECLARE @product_id INT;
DECLARE @quantity INT;
DECLARE @price DECIMAL(10,2);
DECLARE @item VARCHAR(100);
DECLARE @pos INT;

BEGIN TRANSACTION;
BEGIN TRY
— 创建订单
INSERT INTO fgedu.orders (customer_id, total_amount, status)
VALUES (@customer_id, 0, ‘PENDING’);

SET @order_id = SCOPE_IDENTITY();

— 解析产品列表并添加订单项
WHILE LEN(@product_list) > 0
BEGIN
SET @pos = CHARINDEX(‘,’, @product_list);
IF @pos > 0
BEGIN
SET @item = LEFT(@product_list, @pos – 1);
SET @product_list = SUBSTRING(@product_list, @pos + 1, LEN(@product_list));
END
ELSE
BEGIN
SET @item = @product_list;
SET @product_list = ”;
END

— 解析产品ID和数量
SET @product_id = CAST(LEFT(@item, CHARINDEX(‘:’, @item) – 1) AS INT);
SET @quantity = CAST(SUBSTRING(@item, CHARINDEX(‘:’, @item) + 1, LEN(@item)) AS INT);

— 获取产品价格
SELECT @price = price FROM fgedu.products WHERE product_id = @product_id;

— 添加订单项
INSERT INTO fgedu.order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, @product_id, @quantity, @price);

— 计算总金额
SET @total_amount = @total_amount + (@price * @quantity);

— 更新库存
UPDATE fgedu.products SET stock = stock – @quantity WHERE product_id = @product_id;
END

— 更新订单总金额
UPDATE fgedu.orders SET total_amount = @total_amount WHERE order_id = @order_id;

COMMIT TRANSACTION;
PRINT ‘订单创建成功，订单ID: ‘ + CAST(@order_id AS VARCHAR(10));
PRINT ‘订单总金额: ‘ + CAST(@total_amount AS VARCHAR(20));
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
SET @order_id = 0;
PRINT ‘订单创建失败: ‘ + ERROR_MESSAGE();
END CATCH;
END;
GO

— 执行订单创建存储过程
DECLARE @order_id INT;
EXEC fgedu.sp_create_order
@customer_id = 1001,
@product_list = ‘1:2,3:1,4:3’,
@order_id = @order_id OUTPUT;
GO

执行结果：

订单创建成功，订单ID: 1
订单总金额: 13495.00

4.2 SQLServer数据分页存储过程

数据分页存储过程实战：

— 创建分页存储过程
CREATE PROCEDURE fgedu.sp_get_products_paged
@page_index INT = 1,
@page_size INT = 10,
@category VARCHAR(50) = NULL,
@total_count INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

— 获取总记录数
IF @category IS NULL
SELECT @total_count = COUNT(*) FROM fgedu.products;
ELSE
SELECT @total_count = COUNT(*) FROM fgedu.products WHERE category = @category;

— 分页查询
SELECT
product_id,
product_name,
category,
price,
stock
FROM fgedu.products
WHERE (@category IS NULL OR category = @category)
ORDER BY product_id
OFFSET (@page_index – 1) * @page_size ROWS
FETCH NEXT @page_size ROWS ONLY;
END;
GO

— 执行分页存储过程
DECLARE @total INT;
EXEC fgedu.sp_get_products_paged
@page_index = 1,
@page_size = 2,
@category = NULL,
@total_count = @total OUTPUT;
PRINT ‘总记录数: ‘ + CAST(@total AS VARCHAR(10));
GO

— 第二页
DECLARE @total INT;
EXEC fgedu.sp_get_products_paged
@page_index = 2,
@page_size = 2,
@category = NULL,
@total_count = @total OUTPUT;
PRINT ‘总记录数: ‘ + CAST(@total AS VARCHAR(10));
GO

执行结果：

product_id product_name category price stock
———– ————- ———- ——– —–
1 笔记本电脑 电子产品 5999.00 98
2 智能手机 电子产品 3999.00 200
总记录数: 5

product_id product_name category price stock
———– ————- ———- ——– —–
3 机械键盘 电脑配件 399.00 499
4 无线鼠标 电脑配件 99.00 797
总记录数: 5

4.3 SQLServer批量处理存储过程

批量处理存储过程实战：

— 创建批量更新存储过程
CREATE PROCEDURE fgedu.sp_batch_update_prices
@category VARCHAR(50),
@adjustment DECIMAL(5,2) — 调整百分比，如10表示涨价10%
AS
BEGIN
SET NOCOUNT ON;

DECLARE @affected_rows INT;
DECLARE @old_price DECIMAL(10,2);
DECLARE @new_price DECIMAL(10,2);

BEGIN TRANSACTION;
BEGIN TRY
— 记录更新前的价格
SELECT
product_id,
product_name,
price AS old_price,
price * (1 + @adjustment/100) AS new_price
FROM fgedu.products
WHERE category = @category;

— 执行批量更新
UPDATE fgedu.products
SET price = price * (1 + @adjustment/100)
WHERE category = @category;

SET @affected_rows = @@ROWCOUNT;

COMMIT TRANSACTION;
PRINT ‘批量更新完成，影响行数: ‘ + CAST(@affected_rows AS VARCHAR(10));
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT ‘批量更新失败: ‘ + ERROR_MESSAGE();
END CATCH;
END;
GO

— 执行批量更新
EXEC fgedu.sp_batch_update_prices @category = ‘电子产品’, @adjustment = 5;
GO

— 查看更新结果
SELECT product_id, product_name, category, price FROM fgedu.products WHERE category = ‘电子产品’;
GO

执行结果：

product_id product_name old_price new_price
———– ————- ———- ———-
1 笔记本电脑 5999.00 6298.95
2 智能手机 3999.00 4198.95

批量更新完成，影响行数: 2

product_id product_name category price
———– ————- ———- ——–
1 笔记本电脑 电子产品 6298.95
2 智能手机 电子产品 4198.95

Part05-风哥经验总结与分享

5.1 SQLServer存储过程最佳实践

  • 使用SET NOCOUNT ON减少网络流量
  • 添加适当的错误处理机制
  • 使用参数化查询防止SQL注入
  • 保持存储过程简洁，单一职责
  • 添加清晰的注释和文档

5.2 SQLServer存储过程调试技巧

  • 使用PRINT语句输出调试信息
  • 使用TRY-CATCH捕获错误
  • 使用临时表存储中间结果
  • 使用SSMS调试器逐步执行
  • 记录执行日志便于问题追踪

5.3 SQLServer存储过程维护建议

  • 定期检查存储过程性能
  • 更新统计信息确保执行计划最优
  • 使用版本控制管理存储过程代码
  • 定期审查和优化存储过程
  • 建立存储过程变更管理流程

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息