在Databricks Notebook中用Scala调用带多值参数的存储过程

你可以直接把完整的SQL批处理语句（包括变量声明、参数插入、执行存储过程）作为query选项的值传递给JDBC数据源，Spark JDBC驱动会执行整个语句并返回存储过程输出的结果集。

1. 静态参数实现 #

如果参数值固定，直接拼接完整SQL语句即可：

val jdbcURL = "jdbc:sqlserver://your-server:1433;databaseName=your-db"
val jdbcusername = "your-user"
val jdbcpassword = "your-pass"

// 完整SQL批处理，注意去掉SQL Server的GO命令（JDBC不需要该分隔符）
val sqlQuery = """
DECLARE @rc int
DECLARE @p_list something.some_name

INSERT INTO @p_list
SELECT 'value1' UNION ALL
SELECT 'value2' UNION ALL
SELECT 'value3' UNION ALL
SELECT 'value4' UNION ALL
SELECT 'value5'

EXECUTE something.stored_procedure @p_list
"""

// 执行并获取结果DataFrame
val result = spark.read
  .format("com.microsoft.sql.server.jdbc.spark")
  .option("url", jdbcURL)
  .option("user", jdbcusername)
  .option("password", jdbcpassword)
  .option("query", sqlQuery)
  .load()

2. 动态参数实现 #

如果参数值来自Scala变量，用字符串插值动态生成SQL：

// 模拟动态获取的参数列表
val paramValues = List("value1", "value2", "value3", "value4", "value5")

// 生成INSERT语句的拼接部分
val insertClause = paramValues.map(v => s"SELECT '$v'").mkString(" UNION ALL ")

val sqlQuery = s"""
DECLARE @rc int
DECLARE @p_list something.some_name

INSERT INTO @p_list
$insertClause

EXECUTE something.stored_procedure @p_list
"""

// 读取结果
val result = spark.read
  .format("com.microsoft.sql.server.jdbc.spark")
  .option("url", jdbcURL)
  .option("user", jdbcusername)
  .option("password", jdbcpassword)
  .option("query", sqlQuery)
  .load()

注意事项 #

• 确保SQL Server账户拥有执行目标存储过程、操作表值参数类型的权限
• 如果存储过程无结果集返回，spark.read会报错，这种情况建议使用原生JDBC连接（java.sql.Connection）执行语句
• 动态拼接SQL需注意SQL注入风险，若参数来自不可信来源，优先使用原生JDBC的预编译语句处理表值参数

内容的提问来源于stack exchange，提问作者Tamás Godányi