SQLServer存储过程返回结果集异常场景：临时表作用域、事务幽灵锁，动态SQL参数化、执行计划缓存

CREATE PROCEDURE GetEmployeeByDept
    @DeptID INT
AS
BEGIN
    -- 看似正常的查询语句
    SELECT * FROM Employees WHERE DepartmentID = @DeptID
    
    -- 不小心多出来的更新语句
    UPDATE EmployeeStats SET LastQueryTime = GETDATE()
    WHERE DeptID = @DeptID
END

EXEC GetEmployeeByDept @DeptID = 5

ALTER PROCEDURE GetSalesData
AS
BEGIN
    SET NOCOUNT ON; -- 必须加在第一行！
    SELECT * FROM Sales WHERE Year = YEAR(GETDATE())
END

-- 错误调用方式
EXEC GetEmployeeByDept 5

-- 正确显式调用
EXEC GetEmployeeByDept @DeptID = 5

CREATE PROCEDURE SearchProducts
    @SearchTerm NVARCHAR(100)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'SELECT * FROM Products 
                WHERE ProductName LIKE ''%' + @SearchTerm + '%''
                ORDER BY CreateDate DESC'
    
    -- 忘记添加必要的参数处理
    EXEC sp_executesql @SQL
END

CREATE PROCEDURE SafeSearchProducts
    @SearchTerm NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    
    SET @SQL = N'SELECT ProductID, ProductName, UnitPrice 
                FROM Products
                WHERE ProductName LIKE @Term
                ORDER BY CreateDate DESC'
    
    EXEC sp_executesql @SQL, 
                      N'@Term NVARCHAR(100)',
                      @Term = '%' + @SearchTerm + '%'
END

CREATE PROCEDURE CalculateSalesSummary
AS
BEGIN
    CREATE TABLE #TempSales (
        Region NVARCHAR(50),
        TotalSales MONEY
    )
    
    INSERT INTO #TempSales
    SELECT Region, SUM(Amount)
    FROM Sales
    GROUP BY Region
    
    -- 嵌套调用另一个存储过程
    EXEC GenerateSalesReport  -- 该过程也使用了#TempSales表
    
    SELECT * FROM #TempSales
END

-- 使用带唯一标识的临时表名
DECLARE @TempTableName NVARCHAR(100) = 
    '##SalesSummary_' + REPLACE(CAST(NEWID() AS NVARCHAR(36)), '-', '')

DECLARE @SQL NVARCHAR(MAX) = N'
    CREATE TABLE ' + @TempTableName + ' (
        Region NVARCHAR(50),
        TotalSales MONEY
    )'
    
EXEC sp_executesql @SQL

CREATE PROCEDURE UpdateInventory
    @ProductID INT,
    @Qty INT
AS
BEGIN
    BEGIN TRANSACTION
    
    UPDATE Products 
    SET StockQty = StockQty - @Qty
    WHERE ProductID = @ProductID
    
    -- 忘记提交或回滚事务
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION
    ELSE
        -- 缺少COMMIT语句
END

-- 查看当前活动事务
DBCC OPENTRAN

-- 查询锁状态
SELECT 
    resource_type,
    request_mode,
    resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

CREATE PROCEDURE GetOrderHistory
    @CustomerID INT
AS
BEGIN
    SELECT * 
    FROM Orders 
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC
END

-- 方案1：使用本地变量
ALTER PROCEDURE GetOrderHistory
    @CustomerID INT
AS
BEGIN
    DECLARE @LocalCID INT = @CustomerID
    SELECT * 
    FROM Orders 
    WHERE CustomerID = @LocalCID
    ORDER BY OrderDate DESC
END

-- 方案2：优化提示
ALTER PROCEDURE GetOrderHistory
    @CustomerID INT
AS
BEGIN
    SELECT * 
    FROM Orders 
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC
    OPTION (RECOMPILE) -- 每次重新编译
END

-- 开启执行计划跟踪
SET STATISTICS PROFILE ON

EXEC ProblematicProcedure @Param1 = 123

SET STATISTICS PROFILE OFF

CREATE EVENT SESSION [SP_Troubleshooting] 
ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(
        sqlserver.sql_text,
        sqlserver.tsql_stack
    )
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%YourProcedureName%'))
)
ADD TARGET package0.event_file(
    SET filename=N'SP_Troubleshooting.xel'
)
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS
);

CREATE PROCEDURE SafeProc
    @InputDate DATETIME
AS
BEGIN
    -- 日期有效性验证
    IF ISDATE(@InputDate) = 0
    BEGIN
        RAISERROR('Invalid date format', 16, 1)
        RETURN
    END
    
    -- 范围限制
    IF @InputDate < '2000-01-01'
    BEGIN
        SELECT 'Historical data not available' AS Message
        RETURN
    END
    
    -- 主查询逻辑
    SELECT * FROM Events
    WHERE EventDate > @InputDate
END

CREATE PROCEDURE GetFormattedReport
AS
BEGIN
    -- 明确指定列别名和类型
    SELECT 
        CONVERT(NVARCHAR(20), OrderDate, 120) AS FormattedDate,
        CAST(TotalAmount AS DECIMAL(18,2)) AS Amount,
        CASE 
            WHEN Status = 1 THEN 'Active'
            ELSE 'Inactive'
        END AS StatusText
    FROM Orders
END

CREATE PROCEDURE DangerousProc
AS
BEGIN
    BEGIN TRANSACTION
    
    -- DDL语句导致隐式提交
    CREATE INDEX IX_Products_Name ON Products(ProductName)
    
    -- 后续操作不在事务中
    UPDATE Inventory SET Qty = Qty - 10
    WHERE ProductID = 123
    
    COMMIT TRANSACTION -- 此处实际上只提交了UPDATE操作
END

CREATE PROCEDURE PaginateProducts
    @Page INT,
    @PageSize INT
AS
BEGIN
    DECLARE @Offset INT = (@Page - 1) * @PageSize
    
    -- 错误的分页写法
    SELECT *
    FROM Products
    ORDER BY ProductID
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY
    
    -- 缺少总数返回
END

ALTER PROCEDURE PaginateProducts
    @Page INT,
    @PageSize INT
AS
BEGIN
    DECLARE @Offset INT = (@Page - 1) * @PageSize
    
    -- 分页数据
    SELECT ProductID, ProductName, UnitPrice
    FROM Products
    ORDER BY ProductID
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY
    
    -- 总数统计
    SELECT COUNT(*) AS TotalCount
    FROM Products
END