从入门到精通SQLServer存储过程

SQL Server 存储过程：从入门到精通

第一部分：存储过程基础

1. 什么是存储过程？

存储过程是一组预编译的SQL语句，存储在数据库中，可通过名称调用执行。它像是数据库中的"函数"或"程序"。

优点：

• 提高性能（预编译）
• 减少网络流量
• 增强安全性
• 代码重用性
• 易于维护

2. 创建第一个存储过程

-- 基本语法
CREATE PROCEDURE [schema_name.]procedure_name
    @parameter1 datatype [= default_value] [OUTPUT],
    @parameter2 datatype [= default_value]
AS
BEGIN
    -- SQL语句
END
GO

-- 示例：简单的存储过程
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS EmployeeCount FROM Employees;
END
GO

-- 执行存储过程
EXEC GetEmployeeCount;

第二部分：存储过程编程元素

1. 参数传递

-- 输入参数
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT * FROM Employees 
    WHERE DepartmentID = @DepartmentID;
END
GO

-- 带默认值的参数
CREATE PROCEDURE GetProducts
    @CategoryID INT = NULL,
    @MinPrice DECIMAL(10,2) = 0
AS
BEGIN
    SELECT * FROM Products 
    WHERE (CategoryID = @CategoryID OR @CategoryID IS NULL)
      AND Price >= @MinPrice;
END
GO

-- 输出参数
CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID INT,
    @FullName NVARCHAR(100) OUTPUT,
    @Salary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT @FullName = FirstName + ' ' + LastName,
           @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END
GO

-- 使用输出参数
DECLARE @Name NVARCHAR(100), @EmpSalary DECIMAL(10,2);
EXEC GetEmployeeInfo 1, @Name OUTPUT, @EmpSalary OUTPUT;
PRINT 'Employee: ' + @Name + ', Salary: ' + CAST(@EmpSalary AS NVARCHAR);

2. 控制流语句

-- IF...ELSE
CREATE PROCEDURE CheckInventory
    @ProductID INT
AS
BEGIN
    DECLARE @Stock INT;

    SELECT @Stock = QuantityInStock 
    FROM Products WHERE ProductID = @ProductID;

    IF @Stock > 10
        PRINT '库存充足';
    ELSE IF @Stock BETWEEN 1 AND 10
        PRINT '库存紧张';
    ELSE
        PRINT '缺货';
END
GO

-- WHILE循环
CREATE PROCEDURE GenerateTestData
    @RecordCount INT
AS
BEGIN
    DECLARE @Counter INT = 1;

    WHILE @Counter <= @RecordCount
    BEGIN
        INSERT INTO TestTable (Name, CreateDate)
        VALUES ('Test_' + CAST(@Counter AS NVARCHAR(10)), GETDATE());

        SET @Counter = @Counter + 1;
    END
END
GO

-- CASE语句
CREATE PROCEDURE GetOrderStatusDescription
    @OrderID INT
AS
BEGIN
    SELECT 
        OrderID,
        Status,
        Description = CASE Status
            WHEN 1 THEN '已下单'
            WHEN 2 THEN '处理中'
            WHEN 3 THEN '已发货'
            WHEN 4 THEN '已完成'
            WHEN 5 THEN '已取消'
            ELSE '未知状态'
        END
    FROM Orders
    WHERE OrderID = @OrderID;
END
GO

3. 错误处理

-- TRY...CATCH
CREATE PROCEDURE SafeDeleteEmployee
    @EmployeeID INT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 删除相关记录
        DELETE FROM EmployeeProjects WHERE EmployeeID = @EmployeeID;
        DELETE FROM Employees WHERE EmployeeID = @EmployeeID;

        COMMIT TRANSACTION;
        PRINT '员工删除成功';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

-- 自定义错误
CREATE PROCEDURE ValidateOrder
    @OrderID INT
AS
BEGIN
    DECLARE @OrderExists INT;

    SELECT @OrderExists = COUNT(*) 
    FROM Orders WHERE OrderID = @OrderID;

    IF @OrderExists = 0
    BEGIN
        RAISERROR('订单不存在', 16, 1);
        RETURN -1;
    END

    -- 正常处理逻辑
    PRINT '订单验证通过';
END
GO

第三部分：高级存储过程技巧

1. 动态SQL

CREATE PROCEDURE DynamicSearch
    @TableName NVARCHAR(100),
    @SearchColumn NVARCHAR(100),
    @SearchValue NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);

    -- 防止SQL注入
    IF @TableName NOT IN ('Employees', 'Products', 'Orders')
    BEGIN
        RAISERROR('无效的表名', 16, 1);
        RETURN;
    END

    SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName) +
               ' WHERE ' + QUOTENAME(@SearchColumn) + ' = @Value';

    EXEC sp_executesql @SQL, 
          N'@Value NVARCHAR(100)', 
          @Value = @SearchValue;
END
GO

2. 临时表和表变量

CREATE PROCEDURE ComplexReport
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    -- 使用临时表
    CREATE TABLE #TempResults
    (
        OrderID INT,
        CustomerName NVARCHAR(100),
        TotalAmount DECIMAL(10,2),
        OrderDate DATE
    );

    -- 插入数据到临时表
    INSERT INTO #TempResults
    SELECT o.OrderID, c.CustomerName, o.TotalAmount, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate;

    -- 使用表变量
    DECLARE @Summary TABLE
    (
        Category NVARCHAR(50),
        OrderCount INT,
        TotalAmount DECIMAL(10,2)
    );

    INSERT INTO @Summary
    SELECT '高额订单', COUNT(*), SUM(TotalAmount)
    FROM #TempResults
    WHERE TotalAmount > 1000;

    -- 返回结果
    SELECT * FROM #TempResults;
    SELECT * FROM @Summary;

    -- 清理临时表
    DROP TABLE #TempResults;
END
GO

3. 游标使用（谨慎使用）

CREATE PROCEDURE ProcessOrdersBatch
AS
BEGIN
    DECLARE @OrderID INT;
    DECLARE @CustomerID INT;
    DECLARE @TotalAmount DECIMAL(10,2);

    -- 声明游标
    DECLARE order_cursor CURSOR FOR
    SELECT OrderID, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = 1; -- 待处理订单

    OPEN order_cursor;

    -- 获取第一行
    FETCH NEXT FROM order_cursor 
    INTO @OrderID, @CustomerID, @TotalAmount;

    -- 循环处理
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- 业务处理逻辑
            UPDATE Orders SET Status = 2 
            WHERE OrderID = @OrderID;

            PRINT '已处理订单: ' + CAST(@OrderID AS NVARCHAR(10));

            FETCH NEXT FROM order_cursor 
            INTO @OrderID, @CustomerID, @TotalAmount;
        END TRY
        BEGIN CATCH
            PRINT '处理失败: ' + ERROR_MESSAGE();
            CONTINUE;
        END CATCH
    END

    -- 清理
    CLOSE order_cursor;
    DEALLOCATE order_cursor;
END
GO

第四部分：性能优化最佳实践

1. 性能优化技巧

-- 1. 使用SET NOCOUNT ON
CREATE PROCEDURE OptimizedProcedure
    @Param1 INT
AS
BEGIN
    SET NOCOUNT ON;  -- 减少网络流量
    SET XACT_ABORT ON;  -- 错误时自动回滚

    -- 业务逻辑
END
GO

-- 2. 避免不必要的SELECT *
CREATE PROCEDURE GetSpecificColumns
    @EmployeeID INT
AS
BEGIN
    -- 只选择需要的列
    SELECT EmployeeID, FirstName, LastName, Email
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END
GO

-- 3. 使用适当的索引提示
CREATE PROCEDURE GetLargeDataset
    @DepartmentID INT
AS
BEGIN
    SELECT e.*, d.DepartmentName
    FROM Employees e WITH (INDEX(IX_Employees_DepartmentID))
    JOIN Departments d ON e.DepartmentID = d.DepartmentID
    WHERE e.DepartmentID = @DepartmentID
    OPTION (RECOMPILE);  -- 针对当前参数优化
END
GO

2. 分页存储过程

CREATE PROCEDURE GetPagedResults
    @PageNumber INT = 1,
    @PageSize INT = 20,
    @SortColumn NVARCHAR(50) = 'CreateDate',
    @SortDirection NVARCHAR(4) = 'DESC'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

    WITH OrderedResults AS
    (
        SELECT *,
               ROW_NUMBER() OVER (
                   ORDER BY 
                       CASE WHEN @SortColumn = 'Name' AND @SortDirection = 'ASC' 
                            THEN Name END ASC,
                       CASE WHEN @SortColumn = 'Name' AND @SortDirection = 'DESC' 
                            THEN Name END DESC,
                       CASE WHEN @SortColumn = 'CreateDate' AND @SortDirection = 'ASC' 
                            THEN CreateDate END ASC,
                       CASE WHEN @SortColumn = 'CreateDate' AND @SortDirection = 'DESC' 
                            THEN CreateDate END DESC
               ) AS RowNum
        FROM Products
    )
    SELECT * FROM OrderedResults
    WHERE RowNum > @Offset AND RowNum <= @Offset + @PageSize;

    -- 返回总记录数
    SELECT COUNT(*) AS TotalRecords FROM Products;
END
GO

第五部分：实战案例

1. 完整的事务处理示例

CREATE PROCEDURE ProcessOrderTransaction
    @CustomerID INT,
    @OrderDetails OrderDetailsType READONLY  -- 表值参数
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    BEGIN TRY
        -- 1. 插入订单主表
        DECLARE @OrderID INT;
        DECLARE @OrderTotal DECIMAL(10,2);

        SELECT @OrderTotal = SUM(Quantity * UnitPrice)
        FROM @OrderDetails;

        INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, Status)
        VALUES (@CustomerID, GETDATE(), @OrderTotal, 1);

        SET @OrderID = SCOPE_IDENTITY();

        -- 2. 插入订单明细
        INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
        SELECT @OrderID, ProductID, Quantity, UnitPrice
        FROM @OrderDetails;

        -- 3. 更新库存
        UPDATE p
        SET p.QuantityInStock = p.QuantityInStock - od.Quantity
        FROM Products p
        INNER JOIN @OrderDetails od ON p.ProductID = od.ProductID;

        -- 4. 记录日志
        INSERT INTO OrderLog (OrderID, Action, ActionDate)
        VALUES (@OrderID, '订单创建', GETDATE());

        -- 提交事务
        COMMIT TRANSACTION;

        -- 返回成功结果
        SELECT 
            'Success' AS Status,
            @OrderID AS OrderID,
            '订单创建成功' AS Message;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 记录错误日志
        INSERT INTO ErrorLog (ProcedureName, ErrorMessage, ErrorTime)
        VALUES ('ProcessOrderTransaction', ERROR_MESSAGE(), GETDATE());

        -- 返回错误信息
        SELECT 
            'Error' AS Status,
            0 AS OrderID,
            '订单处理失败: ' + ERROR_MESSAGE() AS Message;
    END CATCH
END
GO

-- 创建表值参数类型
CREATE TYPE OrderDetailsType AS TABLE
(
    ProductID INT,
    Quantity INT,
    UnitPrice DECIMAL(10,2)
);
GO

2. 调度任务存储过程

CREATE PROCEDURE DailyMaintenanceTasks
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 备份当天数据
    EXEC BackupDailyData;

    -- 2. 清理过期数据
    DELETE FROM LogTable 
    WHERE LogDate < DATEADD(DAY, -30, GETDATE());

    -- 3. 更新统计信息
    EXEC sp_updatestats;

    -- 4. 重建碎片化索引
    DECLARE @TableName NVARCHAR(255);
    DECLARE @SQL NVARCHAR(MAX);

    DECLARE table_cursor CURSOR FOR
    SELECT name FROM sys.tables 
    WHERE type = 'U';

    OPEN table_cursor;
    FETCH NEXT FROM table_cursor INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = 'ALTER INDEX ALL ON ' + QUOTENAME(@TableName) + ' REORGANIZE';
        EXEC sp_executesql @SQL;

        FETCH NEXT FROM table_cursor INTO @TableName;
    END

    CLOSE table_cursor;
    DEALLOCATE table_cursor;

    -- 5. 发送完成通知
    EXEC SendNotification 'Daily maintenance completed successfully';
END
GO

第六部分：管理和维护

1. 查看和管理存储过程

-- 查看所有存储过程
SELECT 
    name AS ProcedureName,
    create_date AS CreateDate,
    modify_date AS LastModified
FROM sys.procedures
ORDER BY name;

-- 查看存储过程定义
EXEC sp_helptext 'GetEmployeeCount';

-- 查看存储过程依赖
EXEC sp_depends 'GetEmployeeCount';

-- 修改存储过程
ALTER PROCEDURE GetEmployeeCount
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS EmployeeCount FROM Employees
    WHERE Active = 1;  -- 只统计活跃员工
END
GO

-- 删除存储过程
DROP PROCEDURE IF EXISTS GetEmployeeCount;

2. 性能监控

-- 查看存储过程执行统计
SELECT 
    p.name AS ProcedureName,
    ps.execution_count AS ExecutionCount,
    ps.total_worker_time / 1000 AS TotalCPUTime_ms,
    ps.total_elapsed_time / 1000 AS TotalDuration_ms,
    ps.last_execution_time AS LastExecuted
FROM sys.dm_exec_procedure_stats ps
JOIN sys.procedures p ON ps.object_id = p.object_id
ORDER BY ps.total_worker_time DESC;

-- 查看存储过程执行计划
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

EXEC GetEmployeesByDepartment @DepartmentID = 1;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

学习建议

循序渐进：从简单查询开始，逐步增加复杂度 实践为主：每个例子都要亲手编写和测试 理解原理：不仅仅是语法，要理解执行计划和性能影响 代码规范：保持一致的命名和格式规范 错误处理：始终考虑异常情况和错误处理 性能意识：编写时就要考虑性能影响 版本控制：重要的存储过程要进行版本管理

常见问题解决

性能问题：使用执行计划分析器，检查索引使用 阻塞问题：合理设计事务，避免长时间锁表 参数嗅探：使用局部变量或OPTION(RECOMPILE) 动态SQL注入：使用参数化查询或sp_executesql 内存泄漏：及时清理临时表和游标

掌握这些内容后，你将能够设计高效、安全、可维护的SQL Server存储过程解决方案。