Counting number of records returned from a stored procedure (not counting WITH a sp!)

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)

Counting number of records returned from a stored procedure (not counting WITH a sp!)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-19 : 08:52:52

Bard writes "Hello!

On an asp-page, i return data from one of two stored procedures, wich takes either one or two parameters. Passing the parameters to the procedures is not a problem, nor getting/displaying the records in the webpage.
The problem arises when i try to count the number of returned records. I simply don't know how to do it!

I call the two sp's like this (pretty standard...)
Bilag3aKvartal <param #1>
Bilag3aKvartalFO <param #1, param #2>
(As you probably have guessed, param #1 and #2 change based on user input from the webpage).

And no, i cannot use rst.RecordCount.

Any idea (well, solution..) to what i can do i appreciated.
I have tried to search the web for a solution, but found nothing on this particular problem.

Server: SQL server 2000,
Workstation: Windows 2000

Regards
Bard, Norway
(If this question is published, please don't display my email-adress!)"

izaltsman
A custom title

1139 Posts

Posted - 2002-04-19 : 10:03:58

Well, if you are able to modify your stored procs, you could have them return a recordcount as a separate recordset or (better yet) as an output parameter.

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-19 : 11:18:21

You have two options.

1) As izaltsman says, "..you could have them return a recordcount as a separate recordset or (better yet) as an output parameter..." or
2) Modify you ASP code to use a Client Side recordset.

Example for #2:


Dim conn 'As ADODB.Connection
Dim rs 'As ADODB.Recordset
Dim strSql 'As String

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MyConnectionString

strSql = "MyProcedure @Param1 = '" & strParam1 & "' , @param2 = " & intParam2

Set rs= Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3   '3 = adUseClient

rs.Open strSql, conn, 0, 1   '0 = forward only, 1 = read only

Dim lngRecordCount 'As Long
lngRecordCount = rs.RecordCount

Do While Not rs.Eof
   <<< code to execute goes here >>>
   rs.MoveNext
Loop

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

bherman
Starting Member

2 Posts

Posted - 2002-04-22 : 08:45:30

quote:

Well, if you are able to modify your stored procs, you could have them return a recordcount as a separate recordset or (better yet) as an output parameter.

Thanks for the answer, but i fail to accomplish this :(
I have tried to do it like this (with the select-statements that return the coloums snipped):

Declare @antall int
declare @ant1 int
declare @ant2 int
set @ant1 = 
(select count(*) from Person, Info, Plass
where
Person.Info = Info.Indeks
and Person.Plass = Plass.Indeks
and Person.Fjernet = 0
and Info.FlytteOmgang = @flytteOmgang)

set @ant2 = 
(select count(*) antall
from Utstyr, Info, Person, Plass
where
Utstyr.AnsattNr = Person.AnsattNr
and Utstyr.Info = Info.Indeks
and Utstyr.Felles = Plass.Indeks
and Utstyr.fjernet = 0
and Info.FlytteOmgang = @flytteOmgang)

set @antall = @ant1 + @ant2
select @antall as antall

But the asp-page returns an errorcode "Item cannot be found in the collection corresponding to the requested name or ordinal."
This is what i have tried; response.write (" antall: ") & rst(antall).value.
Any more ideas from you ppl? I am stuck.

Regards
Bard

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-22 : 09:08:40

This line is incorrect:

response.write (" antall: ") & rst(antall).value

You need to put quotes around the field (column) name:

response.write (" antall: ") & rst("antall").value

bherman
Starting Member

2 Posts

Posted - 2002-04-24 : 06:14:05

quote:

This line is incorrect:

response.write (" antall: ") & rst(antall).value

You need to put quotes around the field (column) name:

response.write (" antall: ") & rst("antall").value

Oopss! That was just a typo on my part. I had infact quotes around the fieldname. The errormessage still shows up.

BTW: The sp is a union all. This means that the sql for counting comes after the "select ... union all select ...". When executed from query analyzer both result sets are returned, so the query works (at least). Perhaps the asp does not understand that there is two different Select's? That is searches for colomn Antall from the first Select statement where there is no colomn with that name?
Any idea?

Bard

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-24 : 10:32:04

Since you are returning more than one recordset, you will need to use the ADO Recordset's NextRecordset method to get to the second recordset.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthnextrec.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources