关于存储过程返回的结果集读取的问题
之前做了一个系统，这个系统的每个客户都是使用一个独立的数据库（所有数据库都放在同一个服务器上）
现在要做一个销售方的后台管理系统（可以跨数据库进行查询，主要是用于延长某客户的系统使用期限）
我在 master 表上面写了一个存储过程：
CREATE PROCEDURE [dbo].sp_prescription_info
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #dbInfo (
[DB_ID] SMALLINT,
[DB_NAME] SYSNAME,
DB_UID SYSNAME,
DB_CRDATE DATETIME,
DB_SIZE NVARCHAR(13),
DB_VDATE DATETIME
)
DECLARE @sid VARBINARY(85)
DECLARE @name SYSNAME
DECLARE @dbid SMALLINT
DECLARE @crdate DATETIME
DECLARE curDB CURSOR FOR
SELECT name, dbid, crdate, sid FROM sysdatabases WHERE dbid > 4
--创建临时表, 用于获取数据库大小
CREATE TABLE #dbHelp (
name NVARCHAR(24),
db_size NVARCHAR(13),
owner NVARCHAR(24),
dbid SMALLINT,
created CHAR(11),
status VARCHAR(340),
compatibility_level TINYINT
)
INSERT INTO #dbHelp EXEC sp_helpdb
DECLARE @sql NVARCHAR(1000)
OPEN curDB
FETCH curDB INTO @name, @dbid, @crdate, @sid
WHILE (@@FETCH_STATUS = 0)
BEGIN
--检查数据库是否包含特定表及列
DECLARE @isTargetDB INT
SET @sql = N'SELECT @isTargetDB = COUNT(*) FROM ' + @name + '.dbo.syscolumns db
WHERE (name = ''CFG_TYPE'' OR name = ''CFG_VALID'') AND id = (
SELECT db.id FROM ' + @name + '.dbo.sysobjects db
WHERE db.name = ''Config'' AND xtype = ''U'')'
EXEC sp_executesql @sql, N'@isTargetDB INT OUT', @isTargetDB OUT
PRINT @isTargetDB
IF (@isTargetDB = 2)
BEGIN
--检查该列中的值是否符合要求
DECLARE @type NVARCHAR(15)
DECLARE @vdate NVARCHAR(50)
SET @sql = N'SELECT @type = db.CFG_TYPE, @vdate = db.CFG_VALID FROM ' + @name + '.dbo.Config db'
EXEC sp_executesql @sql, N'@type NVARCHAR(15) OUT, @vdate DATETIME OUT', @type OUT, @vdate OUT
IF @type = 'Prescription' AND ISDATE(@vdate) = 1
BEGIN
DECLARE @uid SYSNAME SET @uid = N''
SELECT @uid = loginname FROM syslogins WHERE sid = @sid
INSERT INTO #dbInfo SELECT @dbid, @name, @uid, @crdate, db_size, @vdate FROM #dbHelp WHERE name = @name
END
END
FETCH curDB INTO @name, @dbid, @crdate, @sid
END
CLOSE curDB
DEALLOCATE curDB
DROP TABLE #dbHelp
SELECT * FROM #dbInfo
DROP TABLE #dbInfo
END
GO
// 执行存储过程
int CELConn::ExecuteStored(LPCTSTR pszStoredName, _variant_t *pParamVal, _variant_t **pResult /*= NULL*/, HRECORD *pRecord /*= NULL*/)
{
if (m_hConn == NULL || pszStoredName == NULL) return -1;
_ConnectionPtr pConn = *(_ConnectionPtr *)m_hConn;
_RecordsetPtr *pRecordset = NULL;
int nRtnCount = 0;
BOOL *pbRtnParam = NULL;
CONNTRY
_CommandPtr pCmd = NULL;
HRESULT hr = pCmd.CreateInstance(__uuidof(Command));
if (hr != S_OK) return -1;
pCmd->ActiveConnection = pConn;
pCmd->CommandText = (_bstr_t)pszStoredName;
pCmd->CommandType = adCmdStoredProc;
pCmd->Parameters->Refresh();
long lParamCount = pCmd->Parameters->GetCount();
pbRtnParam = new BOOL[lParamCount];
::memset(pbRtnParam, 0, sizeof(BOOL) * lParamCount);
for (long i = 0; i < lParamCount; i++)
{
_ParameterPtr pParam = pCmd->Parameters->GetItem(i);
ParameterDirectionEnum pde = pParam->Direction;
if (pde == adParamOutput || pde == adParamReturnValue || pde == adParamInputOutput)
{
nRtnCount++;
pbRtnParam[i] = TRUE;
}
if (pParamVal[i].vt != VT_ERROR)
pParam->Value = pParamVal[i];
}
pRecordset = new _RecordsetPtr;
(*pRecordset).CreateInstance(__uuidof(Recordset));
*pRecordset = pCmd->Execute(NULL, NULL, adCmdStoredProc);
// 这里返回的结果集，是关闭状态的，所以无法获取数据
// 但是在 SQL 查询分析器 上面调用这个存储过程，是有数据返回的
if ((*pRecordset)->GetState() == adStateClosed)
_ReleaseRecord((HRECORD &)pRecordset);
if (nRtnCount >= 0 && pResult != NULL)
{
int nCount = 0;
_variant_t *pRtn = new _variant_t[nRtnCount];
for (long i = 0; i < lParamCount; i++)
{
if (pbRtnParam[i])
{
pRtn[nCount] = pCmd->Parameters->GetItem(i)->Value;
nCount++;
}
}
*pResult = pRtn;
}
CONNCATCH(
nRtnCount = -1;
SetLastError(e.Error());
_ReleaseRecord((HRECORD &)pRecordset);
)
if (pbRtnParam != NULL) delete[] pbRtnParam;
if (pRecord != NULL) *pRecord = (HRECORD)pRecordset;
return nRtnCount;
}
// 调用
_variant_t *pvar = NULL;
HRECORD hRecord = NULL;
int nRtn = ExecuteStored(_T("sp_prescription_info"), &vtMissing, &pvar, &hRecord);
if (nRtn == -1 || pvar == NULL || hRecord == NULL)
{
if (pvar != NULL) delete[] pvar;
if (hRecord != NULL) _ReleaseRecord(hRecord);
return NULL;
}
