使用 ODBC CALL 和進程輸出執行 SQL Server Native Client 預存程式
適用於： 
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)
SQL Server 預存程序可以有整數傳回碼和輸出參數。 傳回碼和輸出參數會在來自伺服器的最後一個封包中傳送，因此要等到完全釋放資料列集之後才可供應用程式使用。 如果此命令傳回多個結果，則當 IMultipleResults::GetResult 傳回 DB_S_NORESULT 或是 IMultipleResults 介面完全釋放時 (以先發生者為準)，便可使用輸出參數資料。
重要
盡可能使用 Windows 驗證。 如果無法使用 Windows 驗證，請提示使用者在執行階段輸入認證。 請避免將認證儲存在檔案中。 如果您必須保存認證，則應該用 Win32 Crypto API 加密這些認證。
若要處理傳回碼和輸出參數
建構使用 ODBC CALL 逸出序列的 SQL 語句。 此陳述式應該會針對每個輸入/輸出和輸出參數，以及程序傳回值 (若有) 使用參數標記。 對於輸入參數，您可以使用參數標記，或將程式碼寫入值。
使用 DBBINDING 結構的陣列來建立一組繫結 (每一個參數標記各一個)。
使用 IAccessor::CreateAccessor 方法來建立已定義之參數的存取子。 CreateAccessor 會從一組繫結建立存取子。
填入 DBPARAMS 結構。
呼叫 Execute 命令 (在此情況下，為預存程序的呼叫)。
處理資料列集，並使用 IRowset::Release 方法加以釋放。
處理從預存程序收到的傳回碼和輸出參數值。
範例
此範例示範如何處理資料列集、傳回碼和輸出參數。 並不會處理結果集。 IA64 不支援此範例。
本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。
執行第一個 (Transact-SQL) 程式碼清單，以建立應用程式使用的預存程序。
使用 ole32.lib oleaut32.lib 編譯並執行第二個 (C++) 程式碼清單。 此應用程式會連線至電腦的預設 SQL Server 執行個體。 在某些 Windows 作業系統上，您必須將 (localhost) 或 (local) 變更為 SQL Server 執行個體的名稱。 若要連接到具名執行個體，請將連接字串從 L"(local)" 變更為 L"(local)\\name"，其中 name 是具名執行個體。 根據預設，SQL Server Express 會安裝至具名執行個體。 請確定您的 INCLUDE 環境變數包含包含 sqlncli.h 的目錄。
執行第三個 (Transact-SQL) 程式碼清單，以刪除應用程式使用的預存程序。
USE AdventureWorks2022;
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[myProc]'))
DROP PROCEDURE myProc
GO
CREATE PROCEDURE myProc
@inparam nvarchar(5),,
@outparam int OUTPUT
AS
SELECT Color, ListPrice
FROM Production.Product WHERE Size > @inparam
SELECT @outparam = 100
IF (@outparam > 0)
RETURN 999
ELSE
RETURN 888
GO
// compile with: ole32.lib oleaut32.lib
void InitializeAndEstablishConnection();
#define UNICODE
#define DBINITCONSTANTS
#define INITGUID
#define OLEDBVER 0x0250 // to include correct interfaces
#include <windows.h>
#include <stdio.h>
#include <stddef.h>
#include <iostream>
#include <oledb.h>
#include <oledberr.h>
#include <SQLNCLI.h>
using namespace std;
IDBInitialize* pIDBInitialize = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommandText = NULL;
IRowset* pIRowset = NULL;
ICommandWithParameters* pICommandWithParams = NULL;
IAccessor* pIAccessor = NULL;
IDBProperties* pIDBProperties = NULL;
WCHAR* pStringsBuffer;
DBBINDING* pBindings;
const ULONG nInitProps = 4;
DBPROP InitProperties[nInitProps];
const ULONG nPropSet = 1;
DBPROPSET rgInitPropSet[nPropSet];
HRESULT hr;
HACCESSOR hAccessor;
const ULONG nParams = 3; // Number of parameters in the command
DBPARAMBINDINFO ParamBindInfo[nParams];
ULONG i;
ULONG cbColOffset = 0;
ULONG ParamOrdinals[nParams];
DBROWCOUNT cNumRows = 0;
DBPARAMS Params;
// Declare an array of DBBINDING structures, one for each parameter in the command.
DBBINDING acDBBinding[nParams];
DBBINDSTATUS acDBBindStatus[nParams];
// The following buffer is used to store parameter values.
typedef struct tagSPROCPARAMS {
long lReturnValue;
long outParam;
long inParam;
} SPROCPARAMS;
int main() {
// The command to execute.
WCHAR* wCmdString = L"{? = call myProc(?,?)}";
SPROCPARAMS sprocparams = {0,0,14};
// All the initialization activities in a separate function.
InitializeAndEstablishConnection();
// Create a new activity from the data source object.
if ( FAILED(pIDBInitialize->QueryInterface( IID_IDBCreateSession,
(void**) &pIDBCreateSession))) {
cout << "Failed to access IDBCreateSession interface.\n";
goto EXIT;
}
if (FAILED(pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand))) {
cout << "pIDBCreateSession->CreateSession failed.\n";
goto EXIT;
}
// Create a Command object.
if (FAILED(pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(IUnknown**) &pICommandText))) {
cout << "Failed to access ICommand interface.\n";
goto EXIT;
}
// Set the command text.
if (FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString))) {
cout << "Failed to set command text.\n";
goto EXIT;
}
// No need to describe command parameters (parameter name, data type
// etc) in DBPARAMBINDINFO structure and then SetParameterInfo(). The
// provider obtains this information by calling appropriate helper
// function.
// Describe the consumer buffer by filling in the array of DBBINDING structures.
// Each binding associates a single parameter to the consumer's buffer.
for ( i = 0 ; i < nParams ; i++ ) {
acDBBinding[i].obLength = 0;
acDBBinding[i].obStatus = 0;
acDBBinding[i].pTypeInfo = NULL;
acDBBinding[i].pObject = NULL;
acDBBinding[i].pBindExt = NULL;
acDBBinding[i].dwPart = DBPART_VALUE;
acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
acDBBinding[i].dwFlags = 0;
acDBBinding[i].bScale = 0;
} // end for
acDBBinding[0].iOrdinal = 1;
acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);
acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;
acDBBinding[0].cbMaxLen = sizeof(long);
acDBBinding[0].wType = DBTYPE_I4;
acDBBinding[0].bPrecision = 11;
acDBBinding[1].iOrdinal = 2;
acDBBinding[1].obValue = offsetof(SPROCPARAMS, inParam);
acDBBinding[1].eParamIO = DBPARAMIO_INPUT;
acDBBinding[1].cbMaxLen = sizeof(long);
acDBBinding[1].wType = DBTYPE_I4;
acDBBinding[1].bPrecision = 11;
acDBBinding[2].iOrdinal = 3;
acDBBinding[2].obValue = offsetof(SPROCPARAMS, outParam);
acDBBinding[2].eParamIO = DBPARAMIO_OUTPUT;
acDBBinding[2].cbMaxLen = sizeof(long);
acDBBinding[2].wType = DBTYPE_I4;
acDBBinding[2].bPrecision = 11;
// Create an accessor from the above set of bindings.
hr = pICommandText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);
if (FAILED(hr))
cout << "Failed to get IAccessor interface.\n";
hr = pIAccessor->CreateAccessor( DBACCESSOR_PARAMETERDATA,
nParams,
acDBBinding,
sizeof(SPROCPARAMS),
&hAccessor,
acDBBindStatus);
if (FAILED(hr))
cout << "Failed to create accessor for the defined parameters.\n";
// Fill in DBPARAMS structure for the command execution. This structure
// specifies the parameter values in the command and is then passed to Execute.
Params.pData = &sprocparams;
Params.cParamSets = 1;
Params.hAccessor = hAccessor;
// Execute the command.
if ( FAILED(hr = pICommandText->Execute( NULL,
IID_IRowset,
&Params,
&cNumRows,
(IUnknown **) &pIRowset))) {
cout << "Failed to execute command.\n";
goto EXIT;
}
printf("After command execution but before rowset processing.\n\n");
printf(" Return value = %d\n", sprocparams.lReturnValue);
printf(" Output parameter value = %d\n", sprocparams.outParam);
printf(" These are the same default values set in the application.\n\n\n");
// Result set is not important in this example; release it without processing.
pIRowset->Release();
printf("After processing the result set...\n");
printf(" Return value = %d\n", sprocparams.lReturnValue);
printf(" Output parameter value = %d\n\n", sprocparams.outParam);
// Release memory.
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
pICommandText->Release();
pIDBCreateCommand->Release();
pIDBCreateSession->Release();
if (FAILED(pIDBInitialize->Uninitialize()))
// Uninitialize is not required, but it fails if an interface
// has not been released. This can be used for debugging.
cout << "Problem uninitializing.\n";
pIDBInitialize->Release();
CoUninitialize();
return 0;
EXIT:
if (pIAccessor != NULL)
pIAccessor->Release();
if (pICommandText != NULL)
pICommandText->Release();
if (pIDBCreateCommand != NULL)
pIDBCreateCommand->Release();
if (pIDBCreateSession != NULL)
pIDBCreateSession->Release();
if (pIDBInitialize != NULL)
if (FAILED(pIDBInitialize->Uninitialize()))
// Uninitialize is not required, but it fails if an
// interface has not been released. This can be used for debugging.
cout << "Problem in uninitializing.\n";
pIDBInitialize->Release();
CoUninitialize();
};
void InitializeAndEstablishConnection() {
// Initialize the COM library.
CoInitialize(NULL);
// Obtain access to the SQL Server Native Client OLE DB provider.
hr = CoCreateInstance( CLSID_SQLNCLI11,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void **) &pIDBInitialize);
if (FAILED(hr))
cout << "Failed in CoCreateInstance().\n";
// Initialize the property values needed to establish the connection.
for ( i = 0 ; i < nInitProps ; i++ )
VariantInit(&InitProperties[i].vValue);
// Specify server name.
InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[0].vValue.vt = VT_BSTR;
// Replace "MySqlServer" with proper value.
InitProperties[0].vValue.bstrVal = SysAllocString(L"(local)");
InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[0].colid = DB_NULLID;
// Specify database name.
InitProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal = SysAllocString(L"AdventureWorks");
InitProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[1].colid = DB_NULLID;
InitProperties[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString(L"SSPI");
InitProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[2].colid = DB_NULLID;
// Now that properties are set, construct the DBPROPSET structure
// (rgInitPropSet). The DBPROPSET structure is used to pass an array
// of DBPROP structures (InitProperties) to the SetProperties method.
rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet[0].cProperties = 4;
rgInitPropSet[0].rgProperties = InitProperties;
// Set initialization properties.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
if (FAILED(hr))
cout << "Failed to obtain IDBProperties interface.\n";
hr = pIDBProperties->SetProperties(nPropSet, rgInitPropSet);
if (FAILED(hr))
cout << "Failed to set initialization properties.\n";
pIDBProperties->Release();
// Now establish a connection to the data source.
if (FAILED(pIDBInitialize->Initialize()))
cout << "Problem in initializing.\n";
}
USE AdventureWorks2022;
DROP PROCEDURE myProc
GO