SQL Server存储过程 #

存储过程优点：
├── 预编译，执行效率高
├── 减少网络流量
├── 代码复用
├── 安全性控制
├── 模块化设计
└── 减少SQL注入风险

SQL Server存储过程类型
├── 用户定义存储过程
├── 系统存储过程（sp_开头）
├── 扩展存储过程（xp_开头）
├── CLR存储过程
└── 临时存储过程（#开头）

CREATE { PROC | PROCEDURE } [schema_name.]procedure_name
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [ ; ] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
{
    ENCRYPTION
    | RECOMPILE
    | EXECUTE AS Clause
}

-- 创建简单存储过程
CREATE PROCEDURE usp_get_all_users
AS
BEGIN
    SELECT * FROM users;
END
GO

-- 执行存储过程
EXEC usp_get_all_users;
-- 或
EXECUTE usp_get_all_users;
-- 或省略EXEC（第一条语句）
usp_get_all_users;

-- 输入参数
CREATE PROCEDURE usp_get_user_by_id
    @user_id INT
AS
BEGIN
    SELECT * FROM users WHERE id = @user_id;
END
GO

-- 执行
EXEC usp_get_user_by_id @user_id = 1;
EXEC usp_get_user_by_id 1;  -- 按位置传递

-- 默认参数值
CREATE PROCEDURE usp_get_users_by_status
    @status BIT = 1
AS
BEGIN
    SELECT * FROM users WHERE status = @status;
END
GO

-- 执行
EXEC usp_get_users_by_status;      -- 使用默认值
EXEC usp_get_users_by_status 0;    -- 传递新值

-- 输出参数
CREATE PROCEDURE usp_get_user_count
    @status BIT,
    @count INT OUTPUT
AS
BEGIN
    SELECT @count = COUNT(*) 
    FROM users 
    WHERE status = @status;
END
GO

-- 执行并获取输出
DECLARE @result INT;
EXEC usp_get_user_count @status = 1, @count = @result OUTPUT;
PRINT 'Count: ' + CAST(@result AS VARCHAR);

-- 使用RETURN返回状态
CREATE PROCEDURE usp_check_user
    @user_id INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM users WHERE id = @user_id)
        RETURN 1;  -- 存在
    ELSE
        RETURN 0;  -- 不存在
END
GO

-- 执行并获取返回值
DECLARE @status INT;
EXEC @status = usp_check_user @user_id = 1;
PRINT 'Status: ' + CAST(@status AS VARCHAR);

CREATE PROCEDURE usp_demo_variables
AS
BEGIN
    -- 声明变量
    DECLARE @name NVARCHAR(50);
    DECLARE @age INT = 25;
    DECLARE @now DATETIME = GETDATE();
    
    -- 赋值
    SET @name = 'John';
    
    -- 使用SELECT赋值
    SELECT @name = name FROM users WHERE id = 1;
    
    -- 使用变量
    SELECT @name AS name, @age AS age, @now AS now;
END

CREATE PROCEDURE usp_get_user_info
    @user_id INT
AS
BEGIN
    DECLARE @status BIT;
    
    SELECT @status = status FROM users WHERE id = @user_id;
    
    -- IF...ELSE
    IF @status = 1
        PRINT 'Active user';
    ELSE IF @status = 0
        PRINT 'Inactive user';
    ELSE
        PRINT 'User not found';
    
    -- 多条件
    IF EXISTS (SELECT 1 FROM users WHERE id = @user_id AND salary > 5000)
        PRINT 'High salary user';
END

CREATE PROCEDURE usp_demo_loop
    @count INT
AS
BEGIN
    DECLARE @i INT = 1;
    
    -- WHILE循环
    WHILE @i <= @count
    BEGIN
        PRINT 'Iteration: ' + CAST(@i AS VARCHAR);
        SET @i = @i + 1;
        
        -- BREAK和CONTINUE
        IF @i = 5
            BREAK;  -- 退出循环
        IF @i = 3
            CONTINUE;  -- 跳过本次迭代
    END
END

CREATE PROCEDURE usp_get_user_level
    @user_id INT
AS
BEGIN
    SELECT 
        name,
        salary,
        CASE 
            WHEN salary >= 6000 THEN 'High'
            WHEN salary >= 4000 THEN 'Medium'
            ELSE 'Low'
        END AS salary_level
    FROM users
    WHERE id = @user_id;
END

CREATE PROCEDURE usp_safe_update
    @user_id INT,
    @new_salary DECIMAL(10,2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        
        UPDATE users SET salary = @new_salary WHERE id = @user_id;
        
        IF @@ROWCOUNT = 0
            RAISERROR('User not found', 16, 1);
        
        COMMIT TRANSACTION;
        PRINT 'Update successful';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        PRINT 'Error: ' + ERROR_MESSAGE();
        PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
        PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
        PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR);
        PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
        PRINT 'Error Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
    END CATCH
END

-- 加密存储过程定义
CREATE PROCEDURE usp_secret_procedure
WITH ENCRYPTION
AS
BEGIN
    SELECT * FROM users;
END
GO

-- 加密后无法查看定义
EXEC sp_helptext 'usp_secret_procedure';  -- 返回空

-- 每次执行重新编译
CREATE PROCEDURE usp_dynamic_query
    @column_name NVARCHAR(50)
WITH RECOMPILE
AS
BEGIN
    DECLARE @sql NVARCHAR(1000);
    SET @sql = 'SELECT ' + @column_name + ' FROM users';
    EXEC sp_executesql @sql;
END

-- 指定执行上下文
CREATE PROCEDURE usp_admin_operation
WITH EXECUTE AS OWNER  -- 以所有者身份执行
AS
BEGIN
    SELECT * FROM sensitive_data;
END

-- 创建表类型
CREATE TYPE UserTableType AS TABLE (
    name NVARCHAR(50),
    email VARCHAR(100),
    age INT
);

-- 使用表值参数
CREATE PROCEDURE usp_insert_users
    @users UserTableType READONLY
AS
BEGIN
    INSERT INTO users (name, email, age)
    SELECT name, email, age FROM @users;
END
GO

-- 调用
DECLARE @new_users AS UserTableType;
INSERT INTO @new_users VALUES
    ('User1', 'user1@example.com', 25),
    ('User2', 'user2@example.com', 30);

EXEC usp_insert_users @users = @new_users;

CREATE PROCEDURE usp_search_users
    @column_name NVARCHAR(50),
    @search_value NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(1000);
    DECLARE @param_def NVARCHAR(500);
    
    SET @sql = N'SELECT * FROM users WHERE ' + QUOTENAME(@column_name) + ' = @value';
    SET @param_def = N'@value NVARCHAR(100)';
    
    EXEC sp_executesql @sql, @param_def, @value = @search_value;
END

-- 使用参数化查询
CREATE PROCEDURE usp_safe_search
    @name NVARCHAR(50)
AS
BEGIN
    DECLARE @sql NVARCHAR(1000);
    
    -- 使用QUOTENAME防止注入
    SET @sql = N'SELECT * FROM users WHERE name = @safe_name';
    
    EXEC sp_executesql @sql, N'@safe_name NVARCHAR(50)', @safe_name = @name;
END

-- 修改存储过程
ALTER PROCEDURE usp_get_all_users
AS
BEGIN
    SELECT id, name, email FROM users;
END

-- 删除存储过程
DROP PROCEDURE usp_get_all_users;

-- 安全删除
IF OBJECT_ID('usp_get_all_users', 'P') IS NOT NULL
    DROP PROCEDURE usp_get_all_users;

-- 查看定义
EXEC sp_helptext 'usp_get_all_users';

-- 查看依赖
EXEC sp_depends 'usp_get_all_users';

-- 查看信息
EXEC sp_help 'usp_get_all_users';

存储过程命名规范：
├── 使用usp_前缀（用户存储过程）
├── 使用动词+名词：usp_get_user
├── 避免使用sp_前缀（系统存储过程）
└── 使用有意义的名称

存储过程设计原则：
├── 单一职责
├── 使用参数验证
├── 使用错误处理
├── 添加注释
├── 使用SET NOCOUNT ON
├── 避免长事务
└── 使用事务保护

-- 使用SET NOCOUNT ON减少网络流量
CREATE PROCEDURE usp_optimized
AS
BEGIN
    SET NOCOUNT ON;  -- 不返回受影响行数
    
    -- 使用临时表而非表变量（大数据量）
    -- 使用适当的索引
    -- 避免游标
END