Return a recordset and count of the rows with 1 sp? 3

[ClaytonQ]

All the stored procedures I've written return a recordset. Now I have need of an sp that returns both the recordset and a count of the number of rows in the recordset. It seems like it should be a peice of cake, but I've looked and looked and can't figure it out. Thanks!

 

[hneal98]

Have you tried a cube?

 

[ClaytonQ]

Sounds complicated. Is that the only way?

 

[hneal98]

it really is not complicated. When you say you need to return both the recordset and the rowcount, this would be the most effecient way. What do you do with the record set and row count after you get it back?

 

[ClaytonQ]

I'm going to use the rowcount to dim an array in vbscript and then populate the array with the recordset.

 

[ClaytonQ]

Are you talking about using WITH CUBE when using GROUP BY in a SELECT statement?

The ways that I tried were:

1) I tried to get it to treat the count as a column, so my result set would be like

RowCount IDNumber
-------- --------
3 15
3 4
3 12

or something like that.

2) I tried to get it to send me the recordset as normal and the count as an output parameter. I've never used an output parameter before (being a SQL newbie) and I couldn't figure out how to read it. When I ran the sp I got my recordset, but I didn't know how to read the output parameter's value.

3) I saw that the recordset object in ADO has a recordcount property, but I read that to use it you have to have a static cursor, and not forward only. I can use this as a last resort but it just seems like the sp should be able to do this more efficiently.

Thanks for your help.

 

[ClaytonQ]

I notice when I run an sp in Query Analyzer that returns a recordset, it automatically says how many rows were affected after it displays the recordset. If that information is also returned somehow when the sp is called from ADO, that's all I'd need, right? Does it do that?

 

[hneal98]

I am not clear on how you are using this. Are you reading the SP from VB? If so, it would make more sense to get the record count from the ado.recordset. You could then put it in a variable and use it however you like.

 

[ClaytonQ]

I am reading the SP from VB. ASP 3.0 using vbScript. I can get the record count from the ado.recordset. But to do that, I'll need a static cursor, and to get a static cursor I'll have to create my recordset explicitly instead of letting it be created implicitly by an ado.command object. The static cursor also requires more resources. So I thought it would be a lot easier to just get SQL to tell me how many records there were when it sends them to me. Boy was I wrong! It's so simple in theory that I'm amazed how hard it is to do.
Is it a principle of SQL that an sp can only do one thing?
Thanks for all your help.

 

[hneal98]

Write the command you are using to read your recordset.

 

[sunila7]

if u dont use SET NOCOUNT ON option in ur SP then the no of recordset affected is retunred.... u can ADO's NextRecordsetMethod to get the No of recordset affected from the SP... Hope this is wht u r looking for...

Sunil

 

[ClaytonQ]

That's awesome Sunil. I just read about SET NOCOUNT in Books On-Line, and I read about the NextRecordset method at MSDN. Now, if I use

Set recordset2 = recordset1.NextRecordset(

RecordsAffected

 )

then recordset2 will automatically contain the count. But to get the value out of the recordset, I think I need the column name.

Count = recordset2("ColumnName")

What's the name of the "column" it returns?
Thanks for your help!

 

[JamesLean]

Just reference the ordinal column:

rs(0)

--James

 

[ClaytonQ]

Thanks James. That's just what I needed. I've never used the ordinal references to column names before, because I've always known the column names. That's cool. I'll probably use that again pretty soon.

I have bad news. I got an error message that says: "Current provider does not support returning multiple recordsets from a single execution." My current provider I guess is ASP 3.0. The message reads as if ASP knows that ADO was sending it two recordsets, but it doesn't know what to do with them. So, I guess I can't use this approach.

What about the idea of using an output parameter?

Here's what I've got so far:

CREATE PROCEDURE usp_GetInterests
@PersonID int,
@InterestCount int = Null OUTPUT
AS
SELECT @InterestCount = CAST((SELECT COUNT(*) FROM tblInterests WHERE (intID = @PersonID)) AS int)

SELECT intID, intInterestsID
FROM tblInterests
WHERE (intID = @PersonID)

This seems to work, because when I do this in Query Analyzer:

Declare @Bob int
EXEC usp_GetInterests 20918, @Bob OUTPUT
PRINT @Bob

I get this result:

intID         intInterestsID 
------------- ---------------- 
20918         8

(1 row(s) affected)

1

But when I go to my ASP page and do this:

Dim rsInterests
Set rsInterests = server.CreateObject("ADODB.Recordset")
With cmd 'A command object created earlier
	.ActiveConnection = cnML
	.CommandType = adcmdstoredproc
	.CommandText = "usp_GetInterests"
	.Parameters("@PersonID").Value = strPersonID
	.Parameters("@InterestCount").Value = Null
	.Parameters("@InterestCount").Direction = adParamOutput
	Set rsInterests = .Execute
End With
Dim intInterestCount
intInterestCount = cmd("@InterestCount")
Response.Write intInterestCount

I get nothing. No error message, just nothing. When I change the second to the last line to

intInterestCount = cmd("@PersonID")

it correctly gives me the person's ID.

Am I doing this wrong? Is there an easier way to get ASP to print an OUTPUT parameter from a SQL stored procedure?

I really appreciate all the help you guys have given me.

 

[ClaytonQ]

I found out what the code above was missing. As it turns out, you can't read an output variable with ASP 3.0 until after you close the recordset. Wierd, eh?
The reason I wanted the record count returned with the recordset was so that I could dim my vbscript array with the proper number of elements to hold my recordset, so if I can't read the recordcount first, it does me no good.
What I'm going to do is dim my array without specifying the number of elements, then fill the array.
What I've learned from this experience is :
1) ASP 3.0 can't handle multiple recordsets, though SQL itself can
2) You can't view an output parameter until you close the recordset
3) You can call a column by an ordinal reference
4) Just because it sounds easy, doesn't mean it is

So, I learned a lot. I appreciate all the help you guys have given me. Thank you!

 

[ClaytonQ]

I understand now what hneal98 meant when he said "Have you tried a data cube?" Something like:

Select Count(*), FirstName
FROM Employees
GROUP BY FirstName
WITH CUBE

returns a recordset with the Count in the last row of the recordset.

I could have ran a stored procedure like that, moved to the last record to find the count, dimmed my array with the proper number of elements, moved to the first record, and then populated my array with the recordset.

I could have used .movefirst even with a forward-only cursor, according to MSDN: "You can call the MoveFirst method in a forward-only Recordset object; doing so may cause the provider to re-execute the command that generated the Recordset object."

So, hneal98 gets a star and my apologies for being so thick-headed.

Thanks again, all of you.

 

[hneal98]

Thanks. As for being thick-headed, naaa, I know you were just excersing all your options which is what a good programmer does.