1,216
社区成员
发帖
与我相关
我的任务
分享如何在VB中取回SQLSERVER存储过程中的返回值（类型为Cursor）？
如存储过程声明如下：
/*-------------------------------------*/
CREATE PROC ReturnTest3
@Rs_Cursor1 CURSOR VARYING OUTPUT,
@Rs_Cursor2 CURSOR VARYING OUTPUT
AS
SET @Rs_Cursor1 = CURSOR
FORWARD_ONLY STATIC FOR
SELECT TOP 10 Company_Name FROM company
SET @Rs_Cursor2 = CURSOR
FORWARD_ONLY STATIC FOR
SELECT TOP 10 User_Name FROM User
OPEN @rs_cursor1
OPEN @rs_cursor2
GO
/*-------------------------------------*/
问：对于返回参数为游标（CURSOR），VB中如何处理？
（对于存储过程的执行结果本身记录集，可利用set rs = cmd.execute(.....)来获取。但对于返回参数本身是游标类型，VB中好像没有提供这种方法，还请高手指点。）
/*-------------------------------------*/
CREATE PROC ReturnTest3
@Rs_Cursor1 CURSOR VARYING OUTPUT,
@Rs_Cursor2 CURSOR VARYING OUTPUT
AS
SET @Rs_Cursor1 = CURSOR
FORWARD_ONLY STATIC FOR
SELECT TOP 10 Company_Name FROM company
SET @Rs_Cursor2 = CURSOR
FORWARD_ONLY STATIC FOR
SELECT TOP 10 User_Name FROM User
OPEN @rs_cursor1
OPEN @rs_cursor2
GO
/*-------------------------------------*/
问：对于返回参数为游标（CURSOR），VB中如何处理？
（对于存储过程的执行结果本身记录集，可利用set rs = cmd.execute(.....)来获取。但对于返回参数本身是游标类型，VB中好像没有提供这种方法，还请高手指点。）
...全文
请发表友善的回复…
发表回复
saucer 2005-01-19
- 打赏
- 举报
在ADO里没法用Cursor，这里是文档说明
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_9bzn.asp
Referencing Transact-SQL Cursors
Transact-SQL cursor names and variables are referenced only by Transact-SQL statements; they cannot be referenced by the API functions of OLE DB, ODBC, ADO, and DB-Library. For example, if you use DECLARE CURSOR and OPEN a Transact-SQL cursor, there is no way to use the ODBC SQLFetch or SQLFetchScroll functions to fetch a row from the Transact-SQL cursor. Applications that need cursor processing and are using these APIs should use the cursor support built into the database API instead of Transact-SQL cursors.
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_07_3w6r.asp?frame=true
The database APIs do not support cursor output parameters on stored procedures. A stored procedure that contains a cursor output parameter cannot be executed directly from a database API function. These stored procedures can only be executed from another stored procedure, a trigger, or a Transact-SQL batch or script.
在你这种情形下，你可以返回2个记录集，
CREATE PROC ReturnTest3
AS
BEGIN
SELECT TOP 10 Company_Name FROM company
SELECT TOP 10 User_Name FROM User
END
GO
然后用NextRecordset方法获取第二个记录集
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnextrec.asp?frame=true
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_9bzn.asp
Referencing Transact-SQL Cursors
Transact-SQL cursor names and variables are referenced only by Transact-SQL statements; they cannot be referenced by the API functions of OLE DB, ODBC, ADO, and DB-Library. For example, if you use DECLARE CURSOR and OPEN a Transact-SQL cursor, there is no way to use the ODBC SQLFetch or SQLFetchScroll functions to fetch a row from the Transact-SQL cursor. Applications that need cursor processing and are using these APIs should use the cursor support built into the database API instead of Transact-SQL cursors.
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_07_3w6r.asp?frame=true
The database APIs do not support cursor output parameters on stored procedures. A stored procedure that contains a cursor output parameter cannot be executed directly from a database API function. These stored procedures can only be executed from another stored procedure, a trigger, or a Transact-SQL batch or script.
在你这种情形下，你可以返回2个记录集，
CREATE PROC ReturnTest3
AS
BEGIN
SELECT TOP 10 Company_Name FROM company
SELECT TOP 10 User_Name FROM User
END
GO
然后用NextRecordset方法获取第二个记录集
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnextrec.asp?frame=true
