Problem returning parameters from stored procedure

[dbrom]

When I run this stored procedure:


CREATE PROC testCDs
@TotalRecs int OUTPUT
AS
SELECT cdID, cdFirstName, cdLastName, cdCompany FROM tviCDs
SELECT @TotalRecs = @@ROWCOUNT


in this code:


objCommand = Server.CreateObject("ADODB.Command&quot
objCommand.ActiveConnection = connTviDB
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "testCDs"
objCommand.Parameters.Append(objCommand.CreateParameter("@TotalRecs",adInteger,adParamOutput))
rsSearch = objCommand.Execute();
totalRecs = objCommand.Parameters("@TotalRecs"
Response.Write("'" + totalRecs + "'"


my code returns 'undefined'. So it does not recieve a value from a stored procedure.

Have anyone had any experience with Sprocs returning recordser along with some output parameters? I have no clue what the problem might be...
Even when I replace
SELECT @TotalRecs = @@ROWCOUNT
with, say
SELECT @TotalRecs = 1
I still get the same thing - undefined.

Hope you can help.

Thanks in advance.. <Dmitriy>
dbrom@crosswinds.net

 

[rac2]

Hello Dimitry,

Working from the bottom of your code.

I would write

totalRecs = objCommand.Parameters("@TotalRecs").value;

since I want totalRecs to have a value, not to be a Parameter object. I think your code would create a Parameter object. This may not cause a problem because Response.Write seems to work with either a value or an object in other situations.


It is safe to assume that adInteger, adParamOutput, adCmdStoredProc, and connTviDB are defined somewhere, right?


I would not use an @ in a variable name, but it probably works. The command object parameter does not need to have the same name as the OUTPUT parameter.

When I call a stored procedure I use this format with SQL Server but this may not work with other databases.

objCommand.CommandText = "{CALL testCDs}"

Bottomline, I found that I could not get both a recordset and an OUTPUT parameter value in the same call to a stored procedure. It seems your experience confirms this. See my posting in this forum, number 142369 from a few days ago. If I call the proc without getting the recordset then I get the OUTPUT parameter value. Try commenting out the recordset line and see what happens.

//rsSearch = objCommand.Execute();
objCommand.Execute();

Richard

 

[dbrom]

Thanks for response, Richard!

>It is safe to assume that adInteger, adParamOutput,
>adCmdStoredProc, and connTviDB are defined somewhere, right?

Yes, I incluse the adojavas.inc with constants.

Also, If I so as you suggest
//rsSearch = objCommand.Execute();
objCommand.Execute();

I get the output parameter. Bizarre...

Check out this page, though.
I try to use the first approach (getRows and sproc),
and they supposedly are able to do what we can't do...

http://www.adopenstatic.com/experiments/recordsetpaging.asp

Maybe you'll see there something I did not see.. <Dmitriy>
dbrom@crosswinds.net

 

[dbrom]

By the way, you gave me an idea...

I ran objCommand.Execute() and recieved a number of records.

Then I ran rsSearch = objCommand.Execute() and recieved the recordset.

It is obviously slower than just running rsSearch = objCommand.Execute(), but it is still faster than any other paging approaches.

So unless I will find another solution this is still the best one. <Dmitriy>
dbrom@crosswinds.net

 

[rac2]

Hello <Dmitriy>,

I took a look at that page. It sure does appear that they are getting a value back together with a recordset. Could it be a VBScript/JScript thing?

Thanks for the link, I bookmarked it for future study.

Richard

 

[dbrom]

Hey!

I have actually emailed the guy who wrote the article and (surprise!) he answered my question.

Here is his email:



To return an output parameter *and* a recordset (when using server-side
cursors and SQL Server) you need to *close* the recordset before you can
access the output parameters.

<%
Set objRS = objCommand.Execute
If not objRS.EOF then
arrResults = objRS.getRows
End If

' Need to dispose of recordset here
objRS.Close
Set objRS = Nothing

' Now we can get the output parameter
intOutputParam = objCommand.Parameters(&quot;@ParamName&quot.Value
%>

Can you post this on whatever list you weren't getting answers from? Thanks.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: &quot;Dmitriy Bromberg&quot; <dmitriy_bromberg@hotmail.com>
To: <ken@adOpenStatic.com>
Subject: Question about the article.


: Hey Ken,
:
: I have read your article and I have found it very helpful. It is the best
: resourse on recordset paging I have seen so far.
:
: However, I have a question about the
: stored procedure in the GetRows approach.
:
: For some reason, when my stored
: procedure returns a recordset along with the output parameter, the output
parameter is
: returned as &quot;undefined&quot;.
:
: I have posted the question about returning a recordset
: and output parameter simultaneously on Tek-Tips.com, and I found out that
there are
: several people that cannot do it as well.


I haven't tried it yet but that's what I intend to do in the next half hour or so.
Hope it works...

;-)
<Dmitriy>
dbrom@crosswinds.net