SPROC Recordcount

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_PAGING 'CustomerID, ShipName', 'OrderID',
'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate' ************************
LISTING 1: ASP CODE
Set objConn = CreateObject("ADODB.Connection")
objConn.Open myDSN
Set objRS = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT_WITH_PAGING " & SParms

objRS.Open strSQL, objConn





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

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

AS
DECLARE @blnBringAllRecords BIT
DECLARE @strPageNo VARCHAR(50)
DECLARE @strPageSize VARCHAR(50)
DECLARE @strSkippedRows VARCHAR(50)

DECLARE @strFilterCriteria VARCHAR(8000)
DECLARE @strSimpleFilter VARCHAR(8000)
DECLARE @strSortCriteria VARCHAR(8000)
DECLARE @strGroupCriteria 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
(blnBringAllRecords)

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 @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 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 @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END

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

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



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

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

EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)

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 +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
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
+ @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)

END


--IF WE NEED TO RETURN THE RECORDCOUNT
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables
+ @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria
+ @strGroupCriteria
)
GO

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_PAGING 'CustomerID, ShipName', 'OrderID',
> 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'
> ************************ LISTING 1: ASP CODE
> Set objConn = CreateObject("ADODB.Connection")
> objConn.Open myDSN
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> strSQL = "SELECT_WITH_PAGING " & SParms
>
> objRS.Open strSQL, objConn
>
>
>
>
>
> ************************
> LISTING 2: SPROC
>
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @strFields VARCHAR(4000) ,
> @strPK VARCHAR(100),
> @strTables VARCHAR(4000),
> @intPageNo INT = 1,
> @intPageSize INT = NULL,
> @blnGetRecordCount 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 @blnGetRecordCount = 1
> IF @strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
> )
> ELSE
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
> @strFilterCriteria + @strGroupCriteria
> )
> 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.NextRecordset
reccount=objRS("recordcount")

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.

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.

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.

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=JavaScript %>
<%
var cn,strConn, rs;
cn = new ActiveXObject("ADODB.Connection")
strConn = "provider=sqloledb;data source=xxxxx;user id = xxxx;" +
"password=xxxx;initial catalog=pubs"
cn.open(strConn)
rs = new ActiveXObject("ADODB.Recordset")
//uncomment the following to test optional argument
//cn.RecordCountProblemDemo("test",rs)
//comment the following when testing optional argument
cn.RecordCountProblemDemo(rs)
Response.Write(rs.getstring());
rs.close()
cn.close()
%>

The procedure is:

CREATE PROCEDURE [dbo].[RecordCountProblemDemo]
(@SomeVarThatDoesntGetUsed VARCHAR(11) = NULL)
AS
BEGIN
SET NOCOUNT ON
IF @SomeVarThatDoesntGetUsed IS NULL
SET @SomeVarThatDoesntGetUsed = 'hello'

SELECT au_fname, au_lname,
@SomeVarThatDoesntGetUsed 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.

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...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.

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.

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@NOyahoo.SPAMcom> wrote in message
news:ujsSdW8XFHA.3700@TK2MSFTNGP10.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_PAGING 'CustomerID, ShipName', 'OrderID',
>> 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'
>> ************************ LISTING 1: ASP CODE
>> Set objConn = CreateObject("ADODB.Connection")
>> objConn.Open myDSN
>> Set objRS = Server.CreateObject("ADODB.Recordset")
>>
>> strSQL = "SELECT_WITH_PAGING " & SParms
>>
>> objRS.Open strSQL, objConn
>>
>>
>>
>>
>>
>> ************************
>> LISTING 2: SPROC
>>
>> CREATE PROCEDURE SELECT_WITH_PAGING (
>> @strFields VARCHAR(4000) ,
>> @strPK VARCHAR(100),
>> @strTables VARCHAR(4000),
>> @intPageNo INT = 1,
>> @intPageSize INT = NULL,
>> @blnGetRecordCount 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 @blnGetRecordCount = 1
>> IF @strGroupCriteria != ''
>> EXEC (
>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
>> )
>> ELSE
>> EXEC (
>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>> @strFilterCriteria + @strGroupCriteria
>> )
>> 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.NextRecordset
> reccount=objRS("recordcount")
>
> 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]

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.connection")
cn.open "<valid connection string>"
set cmd=createobject("adodb.command")
cmd.CommandText="OutputTest"
cmd.ActiveConnection= cn
cmd.CommandType = adCmdStoredProc
set params=cmd.parameters
params.append cmd.createparameter("@RETURN_VALUE", _
adInteger, adParamReturnValue)
params.append cmd.createparameter("@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@NOyahoo.SPAMcom> wrote in message
> news:ujsSdW8XFHA.3700@TK2MSFTNGP10.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_PAGING 'CustomerID, ShipName', 'OrderID',
>>> 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'
>>> ************************ LISTING 1: ASP CODE
>>> Set objConn = CreateObject("ADODB.Connection")
>>> objConn.Open myDSN
>>> Set objRS = Server.CreateObject("ADODB.Recordset")
>>>
>>> strSQL = "SELECT_WITH_PAGING " & SParms
>>>
>>> objRS.Open strSQL, objConn
>>>
>>>
>>>
>>>
>>>
>>> ************************
>>> LISTING 2: SPROC
>>>
>>> CREATE PROCEDURE SELECT_WITH_PAGING (
>>> @strFields VARCHAR(4000) ,
>>> @strPK VARCHAR(100),
>>> @strTables VARCHAR(4000),
>>> @intPageNo INT = 1,
>>> @intPageSize INT = NULL,
>>> @blnGetRecordCount 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 @blnGetRecordCount = 1
>>> IF @strGroupCriteria != ''
>>> EXEC (
>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>>> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl
>>> (id)' )
>>> ELSE
>>> EXEC (
>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>>> @strFilterCriteria + @strGroupCriteria
>>> )
>>> 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.NextRecordset
>> reccount=objRS("recordcount")
>>
>> 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.

re: SPROC Recordcount

I have 1 small problem with NextRecordSet property. The below does work, but
I can't use the "NextRecordset" line until I finish my 1st RecordSet Loop.
If I try and move the "NextRecordset" 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("ADODB.Connection")
objConn.Open sDSN

Set objRS = Server.CreateObject("ADODB.Recordset")

sSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open sSQL, objConn

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

Set objRS = objRS.NextRecordset
iNumRecords=objRS("RecordCount")
Response.Write "<br>iNumRecords: " & iNumRecords & "<br>"




"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u$N71l9XFHA.3620@TK2MSFTNGP09.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.connection")
> cn.open "<valid connection string>"
> set cmd=createobject("adodb.command")
> cmd.CommandText="OutputTest"
> cmd.ActiveConnection= cn
> cmd.CommandType = adCmdStoredProc
> set params=cmd.parameters
> params.append cmd.createparameter("@RETURN_VALUE", _
> adInteger, adParamReturnValue)
> params.append cmd.createparameter("@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@NOyahoo.SPAMcom> wrote in message
>> news:ujsSdW8XFHA.3700@TK2MSFTNGP10.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_PAGING 'CustomerID, ShipName', 'OrderID',
>>>> 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate'
>>>> ************************ LISTING 1: ASP CODE
>>>> Set objConn = CreateObject("ADODB.Connection")
>>>> objConn.Open myDSN
>>>> Set objRS = Server.CreateObject("ADODB.Recordset")
>>>>
>>>> strSQL = "SELECT_WITH_PAGING " & SParms
>>>>
>>>> objRS.Open strSQL, objConn
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> ************************
>>>> LISTING 2: SPROC
>>>>
>>>> CREATE PROCEDURE SELECT_WITH_PAGING (
>>>> @strFields VARCHAR(4000) ,
>>>> @strPK VARCHAR(100),
>>>> @strTables VARCHAR(4000),
>>>> @intPageNo INT = 1,
>>>> @intPageSize INT = NULL,
>>>> @blnGetRecordCount BIT = 0,
>>>> @strFilter VARCHAR(8000) = NULL,
>>>> @strSort VARCHAR(8000) = NULL,
>>>> @strGroup VARCHAR(8000) = NULL
>>>> )
>>>>
>>>> AS
>>> <snip>
>>>>
>>>> --IF WE NEED TO RETURN THE RECORDCOUNT
>>>> IF @blnGetRecordCount = 1
>>>> IF @strGroupCriteria != ''
>>>> EXEC (
>>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' +
>>>> @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl
>>>> (id)' )
>>>> ELSE
>>>> EXEC (
>>>> 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables +
>>>> @strFilterCriteria + @strGroupCriteria
>>>> )
>>>> 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.NextRecordset
>>> reccount=objRS("recordcount")
>>>
>>> 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]

re: SPROC Recordcount

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

"Dave Anderson" <GTSPXOESSGOQ@spammotel.com> wrote in message
news:ewihWM9XFHA.3032@TK2MSFTNGP10.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]

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.

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 "NextRecordset" line until I finish my 1st
> RecordSet Loop. If I try and move the "NextRecordset" 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"