使用SQLBindParameter插入后SCOPE_IDENTITY()返回NULL问题咨询

• 核心原因是ODBC驱动执行参数化查询（使用SQLBindParameter）时，默认会调用SQL Server的sp_prepexec系统存储过程来执行参数化语句，插入操作的执行作用域属于sp_prepexec内部，当存储过程执行完成后作用域就结束了。而SCOPE_IDENTITY()的生效范围仅为当前会话、当前作用域，后续单独执行查询语句时已经脱离了插入操作的作用域，因此返回NULL。
• 直接拼接SQL语句执行时，插入操作是作为普通SQL批处理在当前会话的顶层作用域执行，没有额外的存储过程作用域包裹，因此后续查询SCOPE_IDENTITY()时仍然在生效范围内，可以正常返回自增ID。

• 方案1：将插入语句和查询语句合并为同一个批处理执行，不需要分开两次调用SQLExecDirect，示例如下：

std::string name = "testname";
// 插入语句后直接拼接查询语句，合并为一次执行
std::wstring ws = L"INSERT INTO [dbo].[Test] ([Name], [Position]) VALUES (?,1); SELECT SCOPE_IDENTITY();";

RETCODE RetCode = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
            SQL_CHAR, name.length(), 0, (SQLPOINTER)name.c_str(), name.length(), NULL);
// 执行合并后的语句
RetCode = SQLExecDirect(hStmt, (SQLWCHAR*)ws.c_str(), SQL_NTS);

// 直接获取查询结果即可，不需要再次执行查询
long id = -1;
SQLLEN indPtr;
SQLBindCol(hStmt, 1, SQL_C_LONG, &id, 0, &indPtr);
if (SQLFetch(hStmt) == SQL_SUCCESS)
{
    if (indPtr != SQL_NULL_DATA)
    {
        std::cout << "Result id: " << id << std::endl;
    }
}
SQLFreeStmt(hStmt, SQL_CLOSE);

• 方案2：将查询语句替换为SELECT @@IDENTITY，不过要注意@@IDENTITY是全局会话级别的，如果存在触发器等操作写入其他含自增列的表，可能会拿到错误的ID，优先级低于方案1。
• 额外注意：SCOPE_IDENTITY()的返回值是NUMERIC(38,0)类型，建议绑定为SQL_C_UBIGINT类型，对应接收的变量使用64位整数类型，避免类型转换导致的异常。

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