sql-server创建存储过程-入门

------------------------示例1: 最简单的存储过程----------------------------
-- 创建一个简单的存储过程，返回所有员工信息
create procedure get_all_employees
as
begin
    -- 查询语句
    select * from employees;
end;
go

-- 执行存储过程
exec get_all_employees;
--或者
execute get_all_employees;


------------------------示例2: 带参数的存储过程----------------------------
-- 创建带参数的存储过程，根据部门ID获取员工
create procedure get_employees_by_dept
    @dept_id int  -- 输入参数：部门ID
as
begin
    select * from employees where department_id = @dept_id;
end;
go

-- 执行存储过程，传入参数
exec get_employees_by_dept 10;--按参数顺序传入
exec get_employees_by_dept @dept_id = 10; --按参数名传入


------------------------示例3: 带默认参数的存储过程----------------------------
-- 创建带默认参数的存储过程
create procedure get_employee_salary
    @employee_id int,
    @include_bonus bit = 0  -- 默认参数，0表示不包含奖金
as
begin
    if @include_bonus = 1
        --ISNULL(bonus, 0) 函数：这是一个处理NULL值的函数,如果bonus字段为NULL，则返回0，否则返回bonus的实际值
        select employee_id, first_name, last_name, salary, salary + isnull(bonus, 0) as total_compensation
        from employees
        where employee_id = @employee_id;
    else
        select employee_id, first_name, last_name, salary
        from employees
        where employee_id = @employee_id;
end;
go

-- 执行存储过程的几种方式
exec get_employee_salary 100;  -- 只使用必需参数
exec get_employee_salary 100, 0;  -- 显式指定所有参数
exec get_employee_salary @employee_id = 100, @include_bonus = 1;  -- 使用参数名


------------------------示例4: 带输出参数的存储过程----------------------------
-- 创建带输出参数的存储过程
create procedure get_employee_count_by_dept
    @dept_id int,
    @employee_count int output  -- 输出参数
as
begin
    select @employee_count = count(*)
    from employees
    where department_id = @dept_id;
end;
go

-- 执行带输出参数的存储过程
declare @Acccount int;
exec get_employee_count_by_dept 10, @count = @Acccount output;
--显示从存储过程返回的结果将变量@Acccount的值以'employee_count'为列名输出
select @Acccount as 'employee_count';


------------------------示例5: 使用事务的存储过程----------------------------
-- 创建使用事务的存储过程（资金转账示例）
create procedure transfer_funds
    @from_account int,
    @to_account int,
    @amount decimal(18,2),
    --bit 相当于布尔值
    @result bit output  -- 输出参数，1表示成功，0表示失败
as
begin
    --在大多数生产环境的存储过程中，推荐使用 set nocount on 以提高性能。
    --当设置为 ON 时，不返回 DML 语句（如 INSERT、UPDATE、DELETE）影响的行数信息
    set nocount on; --是 SQL Server 中的一个设置命令，它用于控制是否返回表示受影响行数的消息。
    set @result = 0;
    
    begin try
        begin transaction; --标记事务的起点
        
        -- 检查源账户是否有足够余额
        --declare: sql关键字,表示要声明一个@balance变量(全局的变量)
        declare @balance decimal(18,2);
        --查询数据库表accounts中的balance列,把结果赋值给变量@balance
        select @balance = balance from accounts where account_id = @from_account;
        
        if @balance < @amount
        begin
            --50001: 自定义错误编号(必须 >= 50000)
            -- 第二个参数: 错误信息描述
            -- 1: 状态码(0-255)
            throw 50001, '资金不足', 1;
            rollback transaction; --回滚事务
            return; -- 立即退出存储过程,不在继续执行后面内容
        end
        
        -- 从源账户扣除金额
        update accounts set balance = balance - @amount where account_id = @from_account;
        
        -- 向目标账户增加金额
        update accounts set balance = balance + @amount where account_id = @to_account;
        
        commit transaction; --提交事务
        set @result = 1; --成功
    end try
    begin catch
        --@@trancount 是sqlserver的系统变量(全局变量)
        --返回当前连接中处于活动状态的事务数量
        --值为0表示没有活动的事务
        --值大于0表示存在未提交的事务
        if @@trancount > 0
            rollback transaction; --回滚事务
            
        -- 记录错误信息
        insert into error_log(error_message, error_time)
        --使用系统函数 error_message() 获取当前错误消息文本
        --使用系统函数 getdate()获取当前时间
        values(error_message(), getdate());
        
        set @result = 0; --失败
    end catch
end;
go

-- 执行事务存储过程
declare @success bit; --创建一个变量存储执行事务的结果
exec transfer_funds 1001, 1002, 500.00, @success output;
select case when @success = 1 then '转账成功' else '转账失败' end as result;


------------------------示例6: 动态SQL的存储过程----------------------------
-- 创建使用动态SQL的存储过程（通用查询示例）
create procedure dynamic_search_employees
    @search_column varchar(100),
    @search_value varchar(100)
as
begin
    set nocount on; --是 SQL Server 中的一个设置命令，它用于控制是否返回表示受影响行数的消息。
    
    declare @sql nvarchar(max); --定义一个变量@sql
    declare @params nvarchar(100); --定义一个变量@params
    
    -- 构建动态SQL
    --quotename()系统函数,自动为列名添加方括号(如[column_name]),防止SQL注入攻击
    set @sql = 'select employee_id, first_name, last_name, email, hire_date from employees where ' + 
               quotename(@search_column) + ' = @value';
    set @params = '@value varchar(100)';
    
    -- 执行动态SQL
    --sp_executesql：安全地执行动态构建的SQL语句
    exec sp_executesql @sql, @params, @value = @search_value;
end;
go

-- 执行动态SQL存储过程
exec dynamic_search_employees 'last_name', 'Smith';
exec dynamic_search_employees 'department_id', '10';