SQL Server存储过程实战全流程_MsSqlserver

有没有那么一刻，你发现自己又在重复编写几乎相同的sql查询，只是where条件换了一两个？或者，一个复杂的业务逻辑，需要你在应用层和数据库层来回拼接字符串，既容易出错，又难以维护？

有一个报表系统，核心是一个涉及十多张表关联、多重条件筛选的统计查询。起初，逻辑直接写在应用代码里。后来需求微调，需要在三个不同的地方修改同一段sql逻辑。再后来，为了优化性能，需要添加缓存机制… 每一次改动都像一场小心翼翼的“拆弹”。直到引入存储过程，将这颗“炸弹”稳稳地封装在数据库层，开发和维护效率才得到了质的飞跃。今天，就来聊聊这个数据库开发的利器——存储过程。

核心摘要：本文不是罗列语法的手册，而是带你理解为何以及如何用存储过程封装业务逻辑，提升代码安全性、复用性和执行效率。你将掌握创建、修改、执行的全流程，并学会使用变量、参数乃至调用其他过程来构建模块化的数据库逻辑单元。

🎯 主要内容脉络

🔹 存储过程是什么？为什么需要它？

🔹 从“手工炒菜”到“标准化后厨”

🔹 手把手实战：创建、执行与修改

🔹 定义变量与参数传递（输入/输出）

🔹 进阶协作：在存储过程中调用另一个

🔹 注意事项与最佳实践思考

🧠 第一部分：不只是“存储”的“过程”

你可以把数据库想象成一个餐厅的后厨。直接写sql语句，就像每次顾客点单，你都跑到后厨，现场告诉厨师：“西红柿切丁，鸡蛋打散，先炒鸡蛋盛出，再炒西红柿，最后混合加盐加糖…” 效率低下，且容易口误。

而存储过程（stored procedure），就是提前写好的标准化菜谱。当顾客点“西红柿炒蛋”时，你只需喊一声菜名（调用过程），后厨就按固定、优化过的流程自动完成。它的核心优势在于：

- 复用与维护：逻辑一处编写，多处调用。修改只需更新“菜谱”，所有用到的地方自动生效。

- 性能提升：首次执行后，执行计划通常会被缓存，下次调用更快。减少了网络传输（无需传递长sql字符串）。

- 安全增强：可以授予用户执行某个存储过程的权限，而非直接操作底层表的权限，实现更细粒度的安全控制。

- 业务逻辑封装：将复杂的数据处理逻辑留在数据库层，使应用层代码更清晰。

🔨 第二部分：从零开始，打造你的第一个“标准化菜谱”

1. 创建与执行：最基本的架子

创建存储过程使用 create procedure（或简写 create proc）。

-- 创建一个简单的存储过程，获取所有员工信息
create procedure getallemployees
as
begin
    -- 这里是过程体，可以包含复杂的sql逻辑
    select employeeid, firstname, lastname, department
    from employees
    order by lastname;
end;
go

执行它，使用 exec 或 execute：

-- 执行存储过程
exec getallemployees;

2. 让“菜谱”活起来：变量与参数

固定的菜谱不够用。我们需要能根据“顾客口味”（输入参数）调整的菜谱。

定义变量： 使用 declare，变量以 @ 开头。

输入参数： 在过程名后声明，允许外部传入值。

输出参数： 使用 output 关键字，允许将值传回给调用者。

-- 创建一个带输入、输出参数和内部变量的存储过程
create procedure getemployeecountbydepartment
    @deptname nvarchar(50),       -- 输入参数：部门名称
    @employeecount int output     -- 输出参数：员工数量
as
begin
    declare @today date = getdate(); -- 声明并初始化内部变量
    -- 根据输入参数查询，并将结果赋值给输出参数
    select @employeecount = count(*)
    from employees
    where department = @deptname
      and hiredate <= @today; -- 使用内部变量
    -- 也可以同时返回结果集
    select @deptname as department, @employeecount as count, @today as asofdate;
end;
go

执行带参数的存储过程，并获取输出参数的值：

-- 声明一个变量来接收输出参数
declare @countresult int;
-- 执行，传递输入参数，并指定哪个变量接收输出参数
exec getemployeecountbydepartment 
    @deptname = n'销售部',          -- 明确参数名传递，清晰且顺序可换
    @employeecount = @countresult output;
-- 查看输出参数的值
print '销售部的员工数量是：' + cast(@countresult as nvarchar(10));

🔄 第三部分：模块化构建——“菜谱”调用“菜谱”

复杂的宴席由多道菜组成。同样，复杂的数据库逻辑可以由多个存储过程协同完成。这促进了代码的模块化和复用。

-- 假设我们有一个计算奖金的基础过程
create procedure calculatebonus
    @employeeid int,
    @bonusrate decimal(5,2),
    @bonusamount money output
as
begin
    declare @salary money;
    select @salary = salary from employees where employeeid = @employeeid;
    set @bonusamount = @salary * @bonusrate;
end;
go
-- 另一个高阶过程可以调用它
create procedure processmonthlypayroll
    @department nvarchar(50)
as
begin
    -- 先声明变量接收内部调用结果
    declare @bonus money;
    declare @empid int;
    -- 游标（或更好的是使用集合操作）遍历部门员工
    -- 此处为示例，使用简单循环
    declare emp_cursor cursor for
        select employeeid from employees where department = @department;
    open emp_cursor;
    fetch next from emp_cursor into @empid;
    while @@fetch_status = 0
    begin
        -- 🎯 关键点：在这里调用另一个存储过程
        exec calculatebonus 
             @employeeid = @empid,
             @bonusrate = 0.1, -- 假设奖金率10%
             @bonusamount = @bonus output;
        -- 插入薪资记录，其中包含计算出的奖金
        insert into payrollrecords (employeeid, bonus, processdate)
        values (@empid, @bonus, getdate());
        fetch next from emp_cursor into @empid;
    end;
    close emp_cursor;
    deallocate emp_cursor;
    print ‘部门 ‘ + @department + ‘ 的薪资处理完毕。‘;
end;
go

警告： 上述示例使用了游标以清晰展示调用过程，但在实际生产中，应优先考虑基于集合的sql操作，游标可能带来性能问题。

⚡ 第四部分：修改、调试与进阶思考

修改存储过程

使用 alter procedure。注意，这会完全覆盖原有定义。

-- 为 getallemployees 增加一个筛选在职状态的参数
alter procedure getallemployees
    @isactive bit = 1 -- 新增一个带默认值（1-在职）的参数
as
begin
    select employeeid, firstname, lastname, department
    from employees
    where isactive = @isactive -- 使用新参数
    order by lastname;
end;
go