如何在Snowflake存储过程中执行SELECT查询并返回完整结果？

原存储过程仅返回单行单列数据，原因是只执行了一次rs.next()并获取单个列值。要返回查询的完整结果，可通过遍历结果集的所有行和列，将数据整理为JSON格式返回（保持返回类型为VARCHAR）。

修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE COARSDB0P_DB.COUNT_MISMATCH() 
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$  
var command = `SELECT b.TABLE_CATALOG,b.TABLE_SCHEMA,b.TABLE_NAME,b.TABLE_TYPE,b.ROW_COUNT as Dev_COUNT,a.ROW_COUNT as UAT_COUNT 
FROM TABLE A, information_schema.tables B 
WHERE b.TABLE_NAME=a.TABLE_NAMES 
  AND b.TABLE_SCHEMA=a.TABLE_SCHEMA 
  AND b.TABLE_TYPE=a.TABLE_TYPE 
  AND TRY_CAST(b.ROW_COUNT as NUMBER) != TRY_CAST(a.ROW_COUNT as NUMBER);`;

var stmt = snowflake.createStatement({sqlText: command});  
var rs = stmt.execute();

// 初始化数组存储所有结果行
var resultArray = [];
// 获取结果集的列数
var columnCount = rs.getColumnCount();

// 遍历所有行
while (rs.next()) {
    var rowObj = {};
    // 遍历当前行的所有列，映射列名与值
    for (var i = 1; i <= columnCount; i++) {
        var columnName = rs.getColumnName(i);
        rowObj[columnName] = rs.getColumnValue(i);
    }
    resultArray.push(rowObj);
}

// 将结果数组转为JSON字符串返回
return JSON.stringify(resultArray);
$$; 

关键修改点说明 #

• 遍历所有行：使用while(rs.next())循环，直到读取完结果集的所有行
• 映射列名与值：通过getColumnCount()获取列总数，循环每一列，用getColumnName(i)获取列名、getColumnValue(i)获取对应值，将每行数据转为键值对对象
• 返回JSON格式：将收集到的所有行数据转为JSON字符串，确保完整返回所有查询结果

调用存储过程后，会得到包含所有匹配行的JSON字符串，示例格式如下：

[
  {
    "TABLE_CATALOG": "COARSDB0P_DB",
    "TABLE_SCHEMA": "PUBLIC",
    "TABLE_NAME": "TEST_TABLE",
    "TABLE_TYPE": "BASE TABLE",
    "Dev_COUNT": "100",
    "UAT_COUNT": "90"
  },
  {
    "TABLE_CATALOG": "COARSDB0P_DB",
    "TABLE_SCHEMA": "STAGING",
    "TABLE_NAME": "USER_DATA",
    "TABLE_TYPE": "BASE TABLE",
    "Dev_COUNT": "500",
    "UAT_COUNT": "480"
  }
]

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