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_PAGING 'CustomerID, ShipName', 'OrderID',
'Northwind.dbrders', 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

 

[Bob Barrows [MVP]]

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.dbrders', 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

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

 

[Dave Anderson]

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

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

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

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

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

 

[Bob Barrows [MVP]]

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

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

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

 

[Dave Anderson]

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.

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.

 

[scott]

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

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

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

Bob Barrows

 

[Bob Barrows [MVP]]

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]

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

 

[scott]

will the GetRows() method work with a SPROC that returns a recordset, then a
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.

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

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?

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