走了一圈又回来了,目前各位的方法还是和用临时表异曲同工啊。
TO suchgoodpei(pei):
有件事我不明白,你通过存储过程来将结果写入临时表,在客户端直接调用这个表或者是 : hejinghj(小胖子) 所说的视图,这样存储过程会很麻烦吗?
还是说你原先的存储过程里就用了很多不必要的游标啊?因为据我所知存储过程最多可以返回表变量,可是我个人感觉表变量和临时表只不过换了个名称而已。继续关注
TO suchgoodpei(pei):
有件事我不明白,你通过存储过程来将结果写入临时表,在客户端直接调用这个表或者是 : hejinghj(小胖子) 所说的视图,这样存储过程会很麻烦吗?
还是说你原先的存储过程里就用了很多不必要的游标啊?因为据我所知存储过程最多可以返回表变量,可是我个人感觉表变量和临时表只不过换了个名称而已。继续关注
good luckSteps To Reproduce Behavior
In the SQL Server 7.0 Query Analyzer select the test database Pubs.
Create the following stored procedure. This stored procedure returns a recordset and an out parameter count.if exists (select * from sysobjects where id = object_id(N'[dbo].[GetJobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop proc GetJobs
go
create proc GetJobs @id as int, @count as int [out] as
begin
Select @count = Count(*) from jobs where job_id >@id
Select * from jobs where job_id >@id
end
go
Use VC App Wizard to create a new console application and modify the code as follows:#include "stdafx.h"
#include "stdio.h"
#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" no_namespace rename ("EOF", "EOF2") struct InitOle {
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_; int main(int argc, char* argv[])
{
_variant_t varErr((long)0, VT_ERROR);
_CommandPtr comm(__uuidof(Command));
_ConnectionPtr conn(__uuidof(Connection)); _bstr_t connstr="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=(local)";
conn->Open(connstr, "", "", adConnectUnspecified);
comm->ActiveConnection=conn;
comm->CommandText="GetJobs";
comm->CommandType = adCmdStoredProc ;
comm->Parameters->Refresh();
_variant_t recs; comm->Parameters->Item[_variant_t((short)1)]->Value= _variant_t((long)5);
_RecordsetPtr rs = comm->Execute(&recs, &vtMissing,adCmdStoredProc); _variant_t recordcount= comm->Parameters->Item[_variant_t((short)2)]->Value; printf("recordcount = %li\n", (long)recordcount);
return 0;
}
Change the Datasource, User ID and password in the connection string above.
The recordcount variant that the above code returns is of type VT_NULL rather than the number of records that the stored procedure returns.
被返回。就是说你只要用ADO的RecordSet对象来获取存储过程的返回
(与Output参数什么的完全无关,这个存储过程可以完全没有Output
参数),都会获得一堆结果集,可以用RecordSet
对象的NextRecordSet方法来遍历。不好意思,在VC中怎么写我不知道。但是在VB里,像这样就可以了:Dim Conn as ADODB.Connection
dim a as ADODB.RECORDSETset Conn=New ADODB.Connection
Conn.Open .....Set a=Conn.Execute("Exec abc")就完了。a里面会有一堆结果集。