SQL Server exec参数如何安全传递？

EXEC 是用于执行存储过程、用户定义函数、标量值变量或字符串 Transact-SQL 命令的命令,其参数的用法会根据执行的对象不同而有所区别。

执行存储过程

这是 EXEC 最常见的用法，存储过程可以接受输入参数、输出参数和返回值。

基本语法

-- 使用 EXEC
EXEC [ @return_status = ] procedure_name [ [ @parameter1 = ] value1 [ , ...n ] ]
    [ WITH <execute_option> [ , ...n ] ]
-- 使用 EXECUTE (更完整的形式)
EXECUTE [ @return_status = ] procedure_name [ [ @parameter1 = ] value1 [ ... ] ]
    [ WITH RECOMPILE | WITH { ENCRYPTION | NATIVE_COMPILATION } ]

参数详解

参数传递方式

在调用存储过程时,传递参数有几种方式：

按位置传递

这是最简单的方式,直接按照存储过程中参数定义的顺序提供值。

示例： 假设有一个存储过程 GetCustomerInfo，它接受 CustomerID 和 City 两个参数。

-- 创建示例存储过程
CREATE PROCEDURE GetCustomerInfo
    @CustomerID NVARCHAR(5),
    @City NVARCHAR(40)
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerID = @CustomerID AND City = @City;
END
GO
-- 按位置调用：'ALFKI' 对应 @CustomerID, 'London' 对应 @City
EXEC GetCustomerInfo 'ALFKI', 'London';
GO

按名称传递

明确指定参数名和对应的值，这种方式更清晰，不易出错,尤其是在参数很多或部分参数有默认值时。

示例：

-- 按名称调用，顺序可以任意
EXEC GetCustomerInfo @City = 'London', @CustomerID = 'ALFKI';
GO

使用变量传递

将值先存入变量,然后将变量作为参数传递。

示例：

DECLARE @CustID NVARCHAR(5) = 'ALFKI';
DECLARE @CustCity NVARCHAR(40) = 'London';
-- 使用变量传递
EXEC GetCustomerInfo @CustomerID = @CustID, @City = @CustCity;
GO

混合使用

可以混合使用按位置和按名称的方式传递参数，但按位置传递的参数必须在按名称传递的参数之前。

示例 (正确):

-- 第一个参数按位置，第二个参数按名称
EXEC GetCustomerInfo 'ALFKI', @City = 'London';
GO

示例 (错误):

-- 错误！不能在按位置参数后跟按名称参数
-- EXEC GetCustomerInfo @City = 'London', 'ALFKI'; -- 这会报语法错误

处理输出参数和返回值

存储过程不仅可以接收输入参数,还可以返回数据。

输出参数

使用 OUTPUT 关键字定义和传递。

示例： 创建一个存储过程,根据员工ID返回其姓名。

CREATE PROCEDURE GetEmployeeName
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT -- 定义为输出参数
AS
BEGIN
    SELECT @EmployeeName = FirstName + ' ' + LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END
GO
-- 调用并接收输出参数
DECLARE @EmpName NVARCHAR(100);
EXEC GetEmployeeName @EmployeeID = 1, @EmployeeName = @EmpName OUTPUT;
PRINT '员工姓名是: ' + @EmpName;
GO

返回值

使用 RETURN @status 语句返回一个整数值。

示例： 修改存储过程，使其在找到员工时返回 0，否则返回 -1。

CREATE PROCEDURE GetEmployeeNameWithReturn
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        SELECT @EmployeeName = FirstName + ' ' + LastName
        FROM Employees
        WHERE EmployeeID = @EmployeeID;
        RETURN 0; -- 成功
    END
    ELSE
    BEGIN
        SET @EmployeeName = NULL;
        RETURN -1; -- 未找到
    END
END
GO
-- 调用并接收返回值
DECLARE @EmpName NVARCHAR(100);
DECLARE @RetVal INT;
EXEC @RetVal = GetEmployeeNameWithReturn @EmployeeID = 1, @EmployeeName = @EmpName OUTPUT;
IF @RetVal = 0
    PRINT '成功找到员工: ' + @EmpName;
ELSE
    PRINT '未找到该员工。';
GO

执行动态 SQL 字符串

EXEC 也可以用来执行一个包含 SQL 命令的字符串,这在需要根据变量动态构建查询时非常有用。

基本语法

EXEC ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )
    [ { , @string_variable | [ N ] 'tsql_string' } [ + ...n ] ]
    [ AS { LOGIN | USER } = 'name' ]

示例：

DECLARE @TableName NVARCHAR(50) = 'Customers';
DECLARE @City NVARCHAR(40) = 'London';
-- 动态构建并执行 SQL 字符串
EXEC ('SELECT * FROM ' + @TableName + ' WHERE City = ''' + @City + '''');
GO

注意： 动态 SQL 存在SQL注入风险，务必对用户输入进行严格的验证和参数化处理，上面的例子拼接字符串是不安全的，更安全的方式是使用 sp_executesql。

执行用户定义函数

用户定义函数（特别是标量值函数）也可以通过 EXEC 调用,但语法略有不同。

示例： 假设有一个标量函数 dbo.GetCustomerCountByCity。

-- 创建示例函数
CREATE FUNCTION dbo.GetCustomerCountByCity (@CityName NVARCHAR(40))
RETURNS INT
AS
BEGIN
    DECLARE @Count INT;
    SELECT @Count = COUNT(*) FROM Customers WHERE City = @CityName;
    RETURN @Count;
END
GO
-- 调用函数
DECLARE @CustomerCount INT;
SET @CustomerCount = dbo.GetCustomerCountByCity('London'); -- 推荐方式
-- 或者使用 EXEC
-- EXEC @CustomerCount = dbo.GetCustomerCountByCity 'London';
PRINT '伦敦的客户数量是: ' + CAST(@CustomerCount AS VARCHAR(10));
GO

总结与最佳实践

1. 优先使用按名称传递参数：代码可读性更强,不易因参数顺序变化而出错。
2. 使用输出参数返回多个值：当需要从存储过程返回多个结果时，输出参数比使用 SELECT 语句返回结果集更可控。
3. 使用返回值表示状态：约定俗成，0 表示成功,非零表示特定错误代码。
4. 谨慎使用动态 SQL：如果必须使用，优先考虑 sp_executesql 并进行参数化，以防止SQL注入，如果只是简单的字符串拼接,务必确保所有输入都是可信的或经过严格过滤。
5. 注意作用域：在批处理（GO前后）或不同作用域中执行的 EXEC,其变量作用域可能不同。
6. 为存储过程添加注释：清晰注释每个参数的用途、数据类型和是否为输出参数,方便其他开发者使用。