调用SQL Server多存储过程的统一取数方法如何处理参数问题

C#端推荐方案：自动推导存储过程参数+按顺序传值 #

可以借助SqlCommandBuilder.DeriveParameters方法自动从SQL Server拉取目标存储过程的参数定义，无需硬编码参数名，调用时只需按存储过程定义的参数顺序传值即可，改造后的重载方法示例如下：

public static DataTable GetData(string cmd_text, string p1)
{
    DataTable dt = new DataTable();
    SqlConnection conn = GetSqlConnection();
    SqlCommand cmd = new SqlCommand
    {
        CommandText = cmd_text,
        CommandType = CommandType.StoredProcedure,
        Connection = conn
    };

    // 自动获取存储过程参数定义
    conn.Open();
    SqlCommandBuilder.DeriveParameters(cmd);
    conn.Close();

    // 跳过ReturnValue类型的首参数（不需要处理返回值可直接取下标1）
    int startIndex = cmd.Parameters[0].Direction == ParameterDirection.ReturnValue ? 1 : 0;
    // 按顺序赋值，空值转DBNull避免报错
    cmd.Parameters[startIndex].Value = p1 ?? DBNull.Value;

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    {
        da.Fill(dt);
    }
    return dt;
}

如果要兼容任意数量的入参，不需要写多个重载，可以直接用可变参数改造：

public static DataTable GetData(string cmd_text, params object[] parameters)
{
    DataTable dt = new DataTable();
    SqlConnection conn = GetSqlConnection();
    SqlCommand cmd = new SqlCommand
    {
        CommandText = cmd_text,
        CommandType = CommandType.StoredProcedure,
        Connection = conn
    };

    conn.Open();
    SqlCommandBuilder.DeriveParameters(cmd);
    conn.Close();

    int startIndex = cmd.Parameters[0].Direction == ParameterDirection.ReturnValue ? 1 : 0;
    // 循环按顺序给参数赋值
    for (int i = 0; i < parameters.Length; i++)
    {
        if (startIndex + i < cmd.Parameters.Count)
        {
            cmd.Parameters[startIndex + i].Value = parameters[i] ?? DBNull.Value;
        }
    }

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    {
        da.Fill(dt);
    }
    return dt;
}

优化点 #

• 可以给存储过程的参数列表加内存缓存，避免每次调用都查询数据库的系统表拉取参数定义，降低性能开销。
• 如果需要处理输出/返回参数，可额外扩展参数方向的判断逻辑。

SQL端实现方案：通用包装存储过程 #

你设想的包装存储过程方案完全可行，核心逻辑是写一个通用调用存储过程的存储过程，内部动态拼接执行语句，按顺序传入参数值即可，示例代码如下：

CREATE PROCEDURE dbo.USP_Common_ExecSP
    @TargetSPName NVARCHAR(128), -- 要调用的目标存储过程名
    @ParamValues NVARCHAR(MAX)   -- 参数值列表，按顺序用逗号分隔，字符串类型值需加单引号
AS
BEGIN
    SET NOCOUNT ON;
    -- 用QUOTENAME避免存储过程名注入风险
    DECLARE @ExecSQL NVARCHAR(MAX) = N'EXEC ' + QUOTENAME(@TargetSPName) + N' ' + @ParamValues;
    EXEC sp_executesql @ExecSQL;
END

注意事项 #

• 该方案要注意防范SQL注入风险，建议在C#端对传入的参数值做严格校验，或者改用表值参数传递参数值，避免字符串拼接带来的注入隐患。
• 如果需要返回结果集，直接调用该通用存储过程即可正常返回目标存储过程的查询结果。

内容的提问来源于stack exchange，提问作者user12571241