Return recordset + OUTPUT parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return recordset + OUTPUT parameter

Author

Topic

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-21 : 06:49:40

I'm trying to exec a SP which is to return both a recordset and fill in an output parameter in the one go....but not with much success.

create test_proc
@incode integer, @outrecordcount integer output
as
begin
select * from dbo.customer where code > @incode
set @outrecordcount = @@rowcount
end
return

and in vb (6) I'm calling it by...

With adolocalcommand
.commandtext = "test_proc"
.parameters.append .createparameter("@INCODE", adsmallint, adparaminput, , intlocalcustcode)
.parameters.append .createparameter("@OUTRECORDCOUNT, adsmallint, adparamoutput, , intlocalcount)

this give me the recordset, but with no output parameter
set adorslocal = .execute
this gives me no recordset, but with an output parameter.
.execute, , adexecutenorecords
intlocalcount = .parameters("@outrecordcount").value

I want both in the one go please....does anybody have a solution?

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-21 : 07:01:05

use .nextrecordset property of ADO to fetch the next resultset.

Surendra

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-21 : 07:03:51

and insted of
set @outrecordcount = @@rowcount
use
set @outrecordcount = @@rowcount
Select @outrecordcount

Surendra

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-21 : 07:23:53

Problem with that technique would be that I would have to process all of the 1st recordset fully, before I access the 2nd item - which is designed to allow me to provide a 'progress report' as I process the 1st item - catch 22.

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-21 : 07:31:14

quote:
Originally posted by AndrewMurphy

Problem with that technique would be that I would have to process all of the 1st recordset fully, before I access the 2nd item - which is designed to allow me to provide a 'progress report' as I process the 1st item - catch 22.

Store the first recordset into another recordset and process it and by using .nextrecordset fetch the value of your output paramter.
There will be so many expmple/samples are available on net.... just google it.

Surendra

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-21 : 10:19:09

A workable solution, without changing the SP turns out to be to change the connection to client-side. This problem is a 'feature' of server-side recordsets.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 11:33:42

what do you mean change the connection? can you elaborate on this?

Go with the flow & have fun! Else fight the flow

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-22 : 01:22:29

Checkout before using it… Which ADO version you are using it… It should be 2.5/2.6 or above….

The below is a simple example how to use it.

------------ SP --------
DROP PROC myNextproc
GO
CREATE PROC myNextproc AS
SELECT * FROM titles
SELECT * FROM publishers
GO
------------ VB CODE -----
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

cn.Provider = "sqloledb"
cn.Properties("Data Source") = "MyServerName"
cn.Properties("Initial Catalog") = "pubs"
cn.Properties("user ID") = "sa"
cn.Properties("password") = ""
cn.Open

Cmd.CommandText = "myNextProc"
Cmd.CommandType = adCmdStoredProc

Set rs = Cmd.Execute
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset
Wend

Google ".nextrecordset" you will find more samples.

Surendra

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-22 : 05:20:07

".....to be to change the connection to client-side."

When I created the connection I have the option to make it server-side or client-side.
For the purposes of solving this problem (as described), a client-side connection will work...(as will closing the recordset...which I don't want to do until AFTER I get/use the output variable)

I'm trying to find the "5-good + 5-bad reasons to why one uses client-side v servers-side" book...but it doesn't seem to have been written!

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-22 : 06:22:02

You said

quote:
Originally posted by AndrewMurphy

I'm trying to exec a SP which is to return both a recordset and fill in an output parameter in the one go....but not with much success.
…….
…….
I want both in the one go please....does anybody have a solution?

quote:
Originally posted by AndrewMurphy

" When I created the connection I have the option to make it server-side or client-side. ……… a client-side connection will work...(as will closing the recordset...which I don't want to do until AFTER I get/use the output variable)

I'm trying to find the "5-good + 5-bad reasons to why one uses client-side v servers-side" book...but it doesn't seem to have been written!

Dear AndrewMurphy
You want both the recordsets one of them you want to process in your VB application. Then why you are confusing yourself to decide whether it should be server-side or client-side?
If you want those recordsets in front-end it has to be client-side.
Again don’t worry about your original recordset just set your original recordset to temporary recordset ( Set TempRecordset = OriginalRecordset) for processing and close TempRecordset.
And finding out the 5-good + 5-bad reasons you can search on internet.

Surendra

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-22 : 06:56:13

You misunderstand....(as well)
I have only 1 recordset to be returned...I also have an output parameter, which is not the same thing as a recordset.

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-22 : 07:05:21

quote:
Originally posted by AndrewMurphy

You misunderstand....(as well)
I have only 1 recordset to be returned...I also have an output parameter, which is not the same thing as a recordset.

Yes, but when you will use like this (read my previous posts)
set @outrecordcount = @@rowcount
Select @outrecordcount
In front-end it will fetch as recordset only. And to fetch both of them in front end this is the way. You can not pull one as a recordset and one as a output parameter.

Surendra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources