SPROC Recordcount

[scott]

In LISTING 2, I have a SPROC that returns a recordset and a recordcount in
SQL QA. I can access the Recordset with no problem. How can I grab the
Recordcount with ASP code at the same time I'm getting the Recordset? In QA,
the RecordCount displays below the Recordset.

The below USAGE code will display my SPROC results.

USAGE:
EXEC SELECT_WITH_PAG ING 'CustomerID, ShipName', 'OrderID',
'Northwind.dbo. Orders', 3, 10, 1, '', 'OrderDate' *************** *********
LISTING 1: ASP CODE
Set objConn = CreateObject("A DODB.Connection ")
objConn.Open myDSN
Set objRS = Server.CreateOb ject("ADODB.Rec ordset")

strSQL = "SELECT_WITH_PA GING " & SParms

objRS.Open strSQL, objConn





*************** *********
LISTING 2: SPROC

CREATE PROCEDURE SELECT_WITH_PAG ING (
@strFields VARCHAR(4000) ,
@strPK VARCHAR(100),
@strTables VARCHAR(4000),
@intPageNo INT = 1,
@intPageSize INT = NULL,
@blnGetRecordCo unt BIT = 0,
@strFilter VARCHAR(8000) = NULL,
@strSort VARCHAR(8000) = NULL,
@strGroup VARCHAR(8000) = NULL
)

AS
DECLARE @blnBringAllRec ords BIT
DECLARE @strPageNo VARCHAR(50)
DECLARE @strPageSize VARCHAR(50)
DECLARE @strSkippedRows VARCHAR(50)

DECLARE @strFilterCrite ria VARCHAR(8000)
DECLARE @strSimpleFilte r VARCHAR(8000)
DECLARE @strSortCriteri a VARCHAR(8000)
DECLARE @strGroupCriter ia VARCHAR(8000)

DECLARE @intRecordcount INT
DECLARE @intPagecount INT


--******** NORMALIZE THE PAGING CRITERIA
--if no meaningful inputs are provided, we can avoid paging and execute a
more efficient query, so we will set a flag that will help with that
(blnBringAllRec ords)

IF @intPageNo < 1
SET @intPageNo = 1

SET @strPageNo = CONVERT(VARCHAR (50), @intPageNo)

IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO
PAGING
SET @blnBringAllRec ords = 1
ELSE
BEGIN
SET @blnBringAllRec ords = 0
SET @strPageSize = CONVERT(VARCHAR (50), @intPageSize)
SET @strPageNo = CONVERT(VARCHAR (50), @intPageNo)
SET @strSkippedRows = CONVERT(VARCHAR (50), @intPageSize * (@intPageNo -
1))
END


--******** NORMALIZE THE FILTER AND SORTING CRITERIA
--if they are empty, we will avoid filtering and sorting, respectively,
executing more efficient queries

IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCrite ria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilte r = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilte r = ''
SET @strFilterCrite ria = ''
END

IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteri a = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteri a = ''

IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriter ia = ' GROUP BY ' + @strGroup + ' '
ELSE
SET @strGroupCriter ia = ''



--*************** *********** NOW START DOING THE REAL WORK
--!NOTE: for potentially improved performance, use sp_executesql instead of
EXEC

IF @blnBringAllRec ords = 1 --ignore paging and run a simple select
BEGIN

EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCrite ria +
@strGroupCriter ia + @strSortCriteri a
)

END-- WE HAD TO BRING ALL RECORDS
ELSE --BRING ONLY A PARTICULAR PAGE
BEGIN
IF @intPageNo = 1 --in this case we can execute a more efficient query,
with no subqueries
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables +
@strFilterCrite ria + @strGroupCriter ia + @strSortCriteri a
)
ELSE --execute a structure of subqueries that brings the correct page
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + '
IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables +
' WHERE ' + @strPK + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables
+ @strFilterCrite ria + @strGroupCriter ia + @strSortCriteri a + ') ' +
@strSimpleFilte r +
@strGroupCriter ia +
@strSortCriteri a + ') ' +
@strGroupCriter ia +
@strSortCriteri a
)

END


--IF WE NEED TO RETURN THE RECORDCOUNT
IF @blnGetRecordCo unt = 1
IF @strGroupCriter ia != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables
+ @strFilterCrite ria + @strGroupCriter ia + ') AS tbl (id)'
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCrite ria
+ @strGroupCriter ia
)
GO

[Bob Barrows [MVP]]

Re: SPROC Recordcount

scott wrote:[color=blue]
> In LISTING 2, I have a SPROC that returns a recordset and a
> recordcount in SQL QA. I can access the Recordset with no problem.
> How can I grab the Recordcount with ASP code at the same time I'm
> getting the Recordset? In QA, the RecordCount displays below the
> Recordset.
>
> The below USAGE code will display my SPROC results.
>
> USAGE:
> EXEC SELECT_WITH_PAG ING 'CustomerID, ShipName', 'OrderID',
> 'Northwind.dbo. Orders', 3, 10, 1, '', 'OrderDate'
> *************** ********* LISTING 1: ASP CODE
> Set objConn = CreateObject("A DODB.Connection ")
> objConn.Open myDSN
> Set objRS = Server.CreateOb ject("ADODB.Rec ordset")
>
> strSQL = "SELECT_WITH_PA GING " & SParms
>
> objRS.Open strSQL, objConn
>
>
>
>
>
> *************** *********
> LISTING 2: SPROC
>
> CREATE PROCEDURE SELECT_WITH_PAG ING (
> @strFields VARCHAR(4000) ,
> @strPK VARCHAR(100),
> @strTables VARCHAR(4000),
> @intPageNo INT = 1,
> @intPageSize INT = NULL,
> @blnGetRecordCo unt BIT = 0,
> @strFilter VARCHAR(8000) = NULL,
> @strSort VARCHAR(8000) = NULL,
> @strGroup VARCHAR(8000) = NULL
> )
>
> AS[/color]
<snip>[color=blue]
>
> --IF WE NEED TO RETURN THE RECORDCOUNT
> IF @blnGetRecordCo unt = 1
> IF @strGroupCriter ia != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
> @strTables + @strFilterCrite ria + @strGroupCriter ia + ') AS tbl (id)'
> )
> ELSE
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
> @strFilterCrite ria + @strGroupCriter ia
> )
> GO[/color]

I will leave it to others to comment on this use of dynamic sql (think "sql
injection") ...

You're returning a second resultset containing the count, so you would use
the recordset's NextRecordset method to access it:

set objRS = objRS.NextRecor dset
reccount=objRS( "recordcoun t")

I would be more inclined to use an output parameter, using a Command object
to retrieve the value.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Dave Anderson]

Re: SPROC Recordcount

Bob Barrows [MVP] wrote:[color=blue]
>
> I would be more inclined to use an output parameter, using
> a Command object to retrieve the value.[/color]

Can your preferred SP-as-method-of-connection technique[1] deal with output
parameters?



[1] I don't use it because it seem not to work in JScript
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Re: SPROC Recordcount

Dave Anderson wrote:[color=blue]
> Bob Barrows [MVP] wrote:[color=green]
>>
>> I would be more inclined to use an output parameter, using
>> a Command object to retrieve the value.[/color]
>
> Can your preferred SP-as-method-of-connection technique[1] deal with
> output parameters?
>[/color]

No. That technique can only be used if there are no output parameters whose
values need to be retrieved, and if the developer is not interested in
retrieving the Return parameter value. An explicit Command object must be
used in either of these situations.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Re: SPROC Recordcount

Dave Anderson wrote:[color=blue]
> Bob Barrows [MVP] wrote:[color=green]
>>
>> I would be more inclined to use an output parameter, using
>> a Command object to retrieve the value.[/color]
>
> Can your preferred SP-as-method-of-connection technique[1] deal with
> output parameters?
> [1] I don't use it because it seem not to work in JScript[/color]

It seems to work fine for me:

<%@ Language=JavaSc ript %>
<%
var cn,strConn, rs;
cn = new ActiveXObject(" ADODB.Connectio n")
strConn = "provider=sqlol edb;data source=xxxxx;us er id = xxxx;" +
"password=xxxx; initial catalog=pubs"
cn.open(strConn )
rs = new ActiveXObject(" ADODB.Recordset ")
//uncomment the following to test optional argument
//cn.RecordCountP roblemDemo("tes t",rs)
//comment the following when testing optional argument
cn.RecordCountP roblemDemo(rs)
Response.Write( rs.getstring()) ;
rs.close()
cn.close()
%>

The procedure is:

CREATE PROCEDURE [dbo].[RecordCountProb lemDemo]
(@SomeVarThatDo esntGetUsed VARCHAR(11) = NULL)
AS
BEGIN
SET NOCOUNT ON
IF @SomeVarThatDoe sntGetUsed IS NULL
SET @SomeVarThatDoe sntGetUsed = 'hello'

SELECT au_fname, au_lname,
@SomeVarThatDoe sntGetUsed FROM authors

END

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Dave Anderson]

Re: SPROC Recordcount

scott wrote:[color=blue]
> In LISTING 2, I have a SPROC that returns a recordset and a
> recordcount in SQL QA. I can access the Recordset with no problem.
> How can I grab the Recordcount with ASP code at the same time I'm
> getting the Recordset? In QA, the RecordCount displays below the
> Recordset.[/color]

A useful generic way to get the recordcount is use GetRows() and examine the
dimensions:

RSArray = RS.GetRows()
RecordCount = UBound(RSArray, 2)

http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthgetrows.asp

This has several advantages. The most obvious is that you can order the
output any way you like.

Secondly, you only need one SELECT in your stored procedure.

Furthermore, GetRows() + array iteration is much faster than stepping
through a recordset with MoveNext().

In addition, you can immediately close your connection (or use it for
something else) after GetRows(), which can give you additional performance
gains.

Lastly, if you have a [SELECT Count] to measure the size of a [SELECT],
every change to one requires a change to the other. Measuring the output
array always returns an accurate count, no matter what changes are made to
the SELECT.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Re: SPROC Recordcount

Dave Anderson wrote:[color=blue]
> scott wrote:[color=green]
>> In LISTING 2, I have a SPROC that returns a recordset and a
>> recordcount in SQL QA. I can access the Recordset with no problem.
>> How can I grab the Recordcount with ASP code at the same time I'm
>> getting the Recordset? In QA, the RecordCount displays below the
>> Recordset.[/color]
>
> A useful generic way to get the recordcount is use GetRows() and
> examine the dimensions:
>[/color]

I second this. I can't believe I failed to mention it.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[scott]

Re: SPROC Recordcount

1st off, thanks for educating me on the NextRecordset prop, didn't know such
exists. However, for learning purposes could you either supply a few lines
of code similating retrieving a recordset followed by an output parameter? A
link to an example would be fine.

I didn't realize I could do 2 things at once with ASP.



"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:ujsSdW8XFH A.3700@TK2MSFTN GP10.phx.gbl...[color=blue]
> scott wrote:[color=green]
>> In LISTING 2, I have a SPROC that returns a recordset and a
>> recordcount in SQL QA. I can access the Recordset with no problem.
>> How can I grab the Recordcount with ASP code at the same time I'm
>> getting the Recordset? In QA, the RecordCount displays below the
>> Recordset.
>>
>> The below USAGE code will display my SPROC results.
>>
>> USAGE:
>> EXEC SELECT_WITH_PAG ING 'CustomerID, ShipName', 'OrderID',
>> 'Northwind.dbo. Orders', 3, 10, 1, '', 'OrderDate'
>> *************** ********* LISTING 1: ASP CODE
>> Set objConn = CreateObject("A DODB.Connection ")
>> objConn.Open myDSN
>> Set objRS = Server.CreateOb ject("ADODB.Rec ordset")
>>
>> strSQL = "SELECT_WITH_PA GING " & SParms
>>
>> objRS.Open strSQL, objConn
>>
>>
>>
>>
>>
>> *************** *********
>> LISTING 2: SPROC
>>
>> CREATE PROCEDURE SELECT_WITH_PAG ING (
>> @strFields VARCHAR(4000) ,
>> @strPK VARCHAR(100),
>> @strTables VARCHAR(4000),
>> @intPageNo INT = 1,
>> @intPageSize INT = NULL,
>> @blnGetRecordCo unt BIT = 0,
>> @strFilter VARCHAR(8000) = NULL,
>> @strSort VARCHAR(8000) = NULL,
>> @strGroup VARCHAR(8000) = NULL
>> )
>>
>> AS[/color]
> <snip>[color=green]
>>
>> --IF WE NEED TO RETURN THE RECORDCOUNT
>> IF @blnGetRecordCo unt = 1
>> IF @strGroupCriter ia != ''
>> EXEC (
>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>> @strTables + @strFilterCrite ria + @strGroupCriter ia + ') AS tbl (id)'
>> )
>> ELSE
>> EXEC (
>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>> @strFilterCrite ria + @strGroupCriter ia
>> )
>> GO[/color]
>
> I will leave it to others to comment on this use of dynamic sql (think
> "sql
> injection") ...
>
> You're returning a second resultset containing the count, so you would use
> the recordset's NextRecordset method to access it:
>
> set objRS = objRS.NextRecor dset
> reccount=objRS( "recordcoun t")
>
> I would be more inclined to use an output parameter, using a Command
> object
> to retrieve the value.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]

[Bob Barrows [MVP]]

Re: SPROC Recordcount

OK, here's a simple procedure:

create procedure OutputTest (
@out int output) AS
BEGIN
SET NOCOUNT ON
SET @out = 33
exec sp_who2
END

In ASP, you would do this (I'm using the ADO constants for readability. You
should #include the adovbs.inc file, or use one of the methods shown here to
define the constants - http://www.aspfaq.com/show.asp?id=2112):

<%
dim cn, cmd, rs, params
set cn=createobject ("adodb.connect ion")
cn.open "<valid connection string>"
set cmd=createobjec t("adodb.comman d")
cmd.CommandText ="OutputTest "
cmd.ActiveConne ction= cn
cmd.CommandType = adCmdStoredProc
set params=cmd.para meters
params.append cmd.createparam eter("@RETURN_V ALUE", _
adInteger, adParamReturnVa lue)
params.append cmd.createparam eter("@out", adInteger, _
adParamOutput)
set rs=cmd.Execute
'the recordset has to be "consumed" before the output
'value is available, so:
response.write rs.GetString
rs.close
response.write "output parameter: " & params(1).value
cn.close:set cn=nothing
set rs=nothing
%>

But again, GetRows is a better solution for your situation.

Bob Barrows
scott wrote:[color=blue]
> 1st off, thanks for educating me on the NextRecordset prop, didn't
> know such exists. However, for learning purposes could you either
> supply a few lines of code similating retrieving a recordset followed
> by an output parameter? A link to an example would be fine.
>
> I didn't realize I could do 2 things at once with ASP.
>
>
>
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:ujsSdW8XFH A.3700@TK2MSFTN GP10.phx.gbl...[color=green]
>> scott wrote:[color=darkred]
>>> In LISTING 2, I have a SPROC that returns a recordset and a
>>> recordcount in SQL QA. I can access the Recordset with no problem.
>>> How can I grab the Recordcount with ASP code at the same time I'm
>>> getting the Recordset? In QA, the RecordCount displays below the
>>> Recordset.
>>>
>>> The below USAGE code will display my SPROC results.
>>>
>>> USAGE:
>>> EXEC SELECT_WITH_PAG ING 'CustomerID, ShipName', 'OrderID',
>>> 'Northwind.dbo. Orders', 3, 10, 1, '', 'OrderDate'
>>> *************** ********* LISTING 1: ASP CODE
>>> Set objConn = CreateObject("A DODB.Connection ")
>>> objConn.Open myDSN
>>> Set objRS = Server.CreateOb ject("ADODB.Rec ordset")
>>>
>>> strSQL = "SELECT_WITH_PA GING " & SParms
>>>
>>> objRS.Open strSQL, objConn
>>>
>>>
>>>
>>>
>>>
>>> *************** *********
>>> LISTING 2: SPROC
>>>
>>> CREATE PROCEDURE SELECT_WITH_PAG ING (
>>> @strFields VARCHAR(4000) ,
>>> @strPK VARCHAR(100),
>>> @strTables VARCHAR(4000),
>>> @intPageNo INT = 1,
>>> @intPageSize INT = NULL,
>>> @blnGetRecordCo unt BIT = 0,
>>> @strFilter VARCHAR(8000) = NULL,
>>> @strSort VARCHAR(8000) = NULL,
>>> @strGroup VARCHAR(8000) = NULL
>>> )
>>>
>>> AS[/color]
>> <snip>[color=darkred]
>>>
>>> --IF WE NEED TO RETURN THE RECORDCOUNT
>>> IF @blnGetRecordCo unt = 1
>>> IF @strGroupCriter ia != ''
>>> EXEC (
>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>>> @strTables + @strFilterCrite ria + @strGroupCriter ia + ') AS tbl
>>> (id)' )
>>> ELSE
>>> EXEC (
>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>>> @strFilterCrite ria + @strGroupCriter ia
>>> )
>>> GO[/color]
>>
>> I will leave it to others to comment on this use of dynamic sql
>> (think "sql
>> injection") ...
>>
>> You're returning a second resultset containing the count, so you
>> would use the recordset's NextRecordset method to access it:
>>
>> set objRS = objRS.NextRecor dset
>> reccount=objRS( "recordcoun t")
>>
>> I would be more inclined to use an output parameter, using a Command
>> object
>> to retrieve the value.
>>
>> Bob Barrows
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.[/color][/color]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[scott]

Re: SPROC Recordcount

I have 1 small problem with NextRecordSet property. The below does work, but
I can't use the "NextRecord set" line until I finish my 1st RecordSet Loop.
If I try and move the "NextRecord set" part at beginning, I get errors. I'd
like to be able to display the iNumRecords (total records rendered by
NextRecordset) before I display my Recordset table. Is there a way around
this?


Set objConn = CreateObject("A DODB.Connection ")
objConn.Open sDSN

Set objRS = Server.CreateOb ject("ADODB.Rec ordset")

sSQL = "SELECT_WITH_PA GING " & SParms
objRS.Open sSQL, objConn

Do While Not objRS.EOF
' Display Records
objRS.MoveNext
Loop

Set objRS = objRS.NextRecor dset
iNumRecords=obj RS("RecordCount ")
Response.Write "<br>iNumRecord s: " & iNumRecords & "<br>"




"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:u$N71l9XFH A.3620@TK2MSFTN GP09.phx.gbl...[color=blue]
> OK, here's a simple procedure:
>
> create procedure OutputTest (
> @out int output) AS
> BEGIN
> SET NOCOUNT ON
> SET @out = 33
> exec sp_who2
> END
>
> In ASP, you would do this (I'm using the ADO constants for readability.
> You
> should #include the adovbs.inc file, or use one of the methods shown here
> to
> define the constants - http://www.aspfaq.com/show.asp?id=2112):
>
> <%
> dim cn, cmd, rs, params
> set cn=createobject ("adodb.connect ion")
> cn.open "<valid connection string>"
> set cmd=createobjec t("adodb.comman d")
> cmd.CommandText ="OutputTest "
> cmd.ActiveConne ction= cn
> cmd.CommandType = adCmdStoredProc
> set params=cmd.para meters
> params.append cmd.createparam eter("@RETURN_V ALUE", _
> adInteger, adParamReturnVa lue)
> params.append cmd.createparam eter("@out", adInteger, _
> adParamOutput)
> set rs=cmd.Execute
> 'the recordset has to be "consumed" before the output
> 'value is available, so:
> response.write rs.GetString
> rs.close
> response.write "output parameter: " & params(1).value
> cn.close:set cn=nothing
> set rs=nothing
> %>
>
> But again, GetRows is a better solution for your situation.
>
> Bob Barrows
> scott wrote:[color=green]
>> 1st off, thanks for educating me on the NextRecordset prop, didn't
>> know such exists. However, for learning purposes could you either
>> supply a few lines of code similating retrieving a recordset followed
>> by an output parameter? A link to an example would be fine.
>>
>> I didn't realize I could do 2 things at once with ASP.
>>
>>
>>
>> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
>> news:ujsSdW8XFH A.3700@TK2MSFTN GP10.phx.gbl...[color=darkred]
>>> scott wrote:
>>>> In LISTING 2, I have a SPROC that returns a recordset and a
>>>> recordcount in SQL QA. I can access the Recordset with no problem.
>>>> How can I grab the Recordcount with ASP code at the same time I'm
>>>> getting the Recordset? In QA, the RecordCount displays below the
>>>> Recordset.
>>>>
>>>> The below USAGE code will display my SPROC results.
>>>>
>>>> USAGE:
>>>> EXEC SELECT_WITH_PAG ING 'CustomerID, ShipName', 'OrderID',
>>>> 'Northwind.dbo. Orders', 3, 10, 1, '', 'OrderDate'
>>>> *************** ********* LISTING 1: ASP CODE
>>>> Set objConn = CreateObject("A DODB.Connection ")
>>>> objConn.Open myDSN
>>>> Set objRS = Server.CreateOb ject("ADODB.Rec ordset")
>>>>
>>>> strSQL = "SELECT_WITH_PA GING " & SParms
>>>>
>>>> objRS.Open strSQL, objConn
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> *************** *********
>>>> LISTING 2: SPROC
>>>>
>>>> CREATE PROCEDURE SELECT_WITH_PAG ING (
>>>> @strFields VARCHAR(4000) ,
>>>> @strPK VARCHAR(100),
>>>> @strTables VARCHAR(4000),
>>>> @intPageNo INT = 1,
>>>> @intPageSize INT = NULL,
>>>> @blnGetRecordCo unt BIT = 0,
>>>> @strFilter VARCHAR(8000) = NULL,
>>>> @strSort VARCHAR(8000) = NULL,
>>>> @strGroup VARCHAR(8000) = NULL
>>>> )
>>>>
>>>> AS
>>> <snip>
>>>>
>>>> --IF WE NEED TO RETURN THE RECORDCOUNT
>>>> IF @blnGetRecordCo unt = 1
>>>> IF @strGroupCriter ia != ''
>>>> EXEC (
>>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>>>> @strTables + @strFilterCrite ria + @strGroupCriter ia + ') AS tbl
>>>> (id)' )
>>>> ELSE
>>>> EXEC (
>>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>>>> @strFilterCrite ria + @strGroupCriter ia
>>>> )
>>>> GO
>>>
>>> I will leave it to others to comment on this use of dynamic sql
>>> (think "sql
>>> injection") ...
>>>
>>> You're returning a second resultset containing the count, so you
>>> would use the recordset's NextRecordset method to access it:
>>>
>>> set objRS = objRS.NextRecor dset
>>> reccount=objRS( "recordcoun t")
>>>
>>> I would be more inclined to use an output parameter, using a Command
>>> object
>>> to retrieve the value.
>>>
>>> Bob Barrows
>>> --
>>> Microsoft MVP -- ASP/ASP.NET
>>> Please reply to the newsgroup. The email account listed in my From
>>> header is my spam trap, so I don't check it very often. You will get
>>> a quicker response by posting to the newsgroup.[/color][/color]
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]

[scott]

Re: SPROC Recordcount

will the GetRows() method work with a SPROC that returns a recordset, then a
recordcount?

"Dave Anderson" <GTSPXOESSGOQ@s pammotel.com> wrote in message
news:ewihWM9XFH A.3032@TK2MSFTN GP10.phx.gbl...[color=blue]
> scott wrote:[color=green]
>> In LISTING 2, I have a SPROC that returns a recordset and a
>> recordcount in SQL QA. I can access the Recordset with no problem.
>> How can I grab the Recordcount with ASP code at the same time I'm
>> getting the Recordset? In QA, the RecordCount displays below the
>> Recordset.[/color]
>
> A useful generic way to get the recordcount is use GetRows() and examine
> the dimensions:
>
> RSArray = RS.GetRows()
> RecordCount = UBound(RSArray, 2)
>
> http://msdn.microsoft.com/library/en...mthgetrows.asp
>
>
>
> This has several advantages. The most obvious is that you can order the
> output any way you like.
>
> Secondly, you only need one SELECT in your stored procedure.
>
> Furthermore, GetRows() + array iteration is much faster than stepping
> through a recordset with MoveNext().
>
> In addition, you can immediately close your connection (or use it for
> something else) after GetRows(), which can give you additional performance
> gains.
>
> Lastly, if you have a [SELECT Count] to measure the size of a [SELECT],
> every change to one requires a change to the other. Measuring the output
> array always returns an accurate count, no matter what changes are made to
> the SELECT.
>
>
>
> --
> Dave Anderson
>
> Unsolicited commercial email will be read at a cost of $500 per message.
> Use of this email address implies consent to these terms. Please do not
> contact me directly or ask me to contact you directly for assistance. If
> your question is worth asking, it's worth posting.
>[/color]

[Dave Anderson]

Re: SPROC Recordcount

scott wrote:[color=blue]
> will the GetRows() method work with a SPROC that returns
> a recordset, then a recordcount?[/color]

Why would you bother? The dimensions of the array TELL YOU the recordcount.


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Re: SPROC Recordcount

scott wrote:[color=blue]
> I have 1 small problem with NextRecordSet property. The below does
> work, but I can't use the "NextRecord set" line until I finish my 1st
> RecordSet Loop. If I try and move the "NextRecord set" part at
> beginning, I get errors. I'd like to be able to display the
> iNumRecords (total records rendered by NextRecordset) before I
> display my Recordset table. Is there a way around this?
>
>[/color]
Yes. Use GetRows to move your data into an array, then call NextRecordset
(however, the Nextrecordset will no longer be necessary because you'll be
able to use ubound to determine the number of records that were returned
from the procedure ... )

If you're truly adverse to arrays for some reason, you can create a copy of
your recordset using the Clone method.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"