RecordCount with Stored Procedure in SQL Server

[Ken VdB]

Hi everyone,

I have noticed that if I used a stored procedure to populate an ADO
RecordSet it only returns a .RecordCount property if that stored procedure
contains nothing but a SELECT statement. For example, I am using the
following stored procedure to get a list of items in a particular product
category. If I eliminate the part that sets the @OrderDate to the current
date, it returns a proper RecordCount. If I leave it in there (which I need
to do), I get (-1) ever time. This is a problem because then I cannot use
the RecordCount property to warn the user that they are not seeing all
items. Any thoughts are welcome.

Cheers,

Ken.

CREATE PROCEDURE [dbo].[FL_KenTest]
(@CategoryCode VARCHAR(5),
@OrderDate SMALLDATETIME = NULL)
AS

BEGIN
IF @OrderDate IS NULL
SET @OrderDate = GETDATE()

--Select just enough items to make the limit of 200 items per
--category go over so the .RecordCount property can be used to
--warn us. But don't waste resources transferring excessive
--amounts of data between tiers.
SELECT TOP 201
p.ItemNumber,
Description
FROM Products
WHERE CategoryCode = @CategoryCode
END

[Aaron Bertrand - MVP]

Ken, can you show table structure (CREATE TABLE), some sample data (INSERT
statements), and the actual code used to call the stored procedure. We
can't reproduce, never mind fix, what we can't see.

(As an aside, instead of using recordcount on the client to see if you
overstepped the boundary, why not just SELECT TOP 200, or SET ROWCOUNT 200?
You could also pass back a separate value in the select statement that tells
you whether or not the actual rowcount may have been higher than the
restricted resultset.)





"Ken VdB" <puffthemagicdragon@nospam.floridus.com> wrote in message
news:uYH$hsylDHA.2820@TK2MSFTNGP10.phx.gbl...

> Hi everyone,
>
> I have noticed that if I used a stored procedure to populate an ADO
> RecordSet it only returns a .RecordCount property if that stored procedure
> contains nothing but a SELECT statement. For example, I am using the
> following stored procedure to get a list of items in a particular product
> category. If I eliminate the part that sets the @OrderDate to the current
> date, it returns a proper RecordCount. If I leave it in there (which I

need

> to do), I get (-1) ever time. This is a problem because then I cannot use
> the RecordCount property to warn the user that they are not seeing all
> items. Any thoughts are welcome.
>
> Cheers,
>
> Ken.
>
> CREATE PROCEDURE [dbo].[FL_KenTest]
> (@CategoryCode VARCHAR(5),
> @OrderDate SMALLDATETIME = NULL)
> AS
>
> BEGIN
> IF @OrderDate IS NULL
> SET @OrderDate = GETDATE()
>
> --Select just enough items to make the limit of 200 items per
> --category go over so the .RecordCount property can be used to
> --warn us. But don't waste resources transferring excessive
> --amounts of data between tiers.
> SELECT TOP 201
> p.ItemNumber,
> Description
> FROM Products
> WHERE CategoryCode = @CategoryCode
> END
>
>

[Tom B]

I think this should work. You'll then get a recordset that contains the
field TotalRecords in every row. I'm sure there's better ways...but...

CREATE PROCEDURE [dbo].[FL_KenTest]
(@CategoryCode VARCHAR(5),
@OrderDate SMALLDATETIME = NULL)
AS

BEGIN
IF @OrderDate IS NULL
SET @OrderDate = GETDATE()
DECLARE @TotalRecords
SELECT @TotalRecords=Count(*) FROM Products WHERE CategoryCode=@CategoryCode

--Select just enough items to make the limit of 200 items per
--category go over so the .RecordCount property can be used to
--warn us. But don't waste resources transferring excessive
--amounts of data between tiers.
SELECT TOP 201
p.ItemNumber,
Description, @TotalRecords
FROM Products
WHERE CategoryCode = @CategoryCode
END


"Ken VdB" <puffthemagicdragon@nospam.floridus.com> wrote in message
news:uYH$hsylDHA.2820@TK2MSFTNGP10.phx.gbl...

> Hi everyone,
>
> I have noticed that if I used a stored procedure to populate an ADO
> RecordSet it only returns a .RecordCount property if that stored procedure
> contains nothing but a SELECT statement. For example, I am using the
> following stored procedure to get a list of items in a particular product
> category. If I eliminate the part that sets the @OrderDate to the current
> date, it returns a proper RecordCount. If I leave it in there (which I

need

> to do), I get (-1) ever time. This is a problem because then I cannot use
> the RecordCount property to warn the user that they are not seeing all
> items. Any thoughts are welcome.
>
> Cheers,
>
> Ken.
>
> CREATE PROCEDURE [dbo].[FL_KenTest]
> (@CategoryCode VARCHAR(5),
> @OrderDate SMALLDATETIME = NULL)
> AS
>
> BEGIN
> IF @OrderDate IS NULL
> SET @OrderDate = GETDATE()
>
> --Select just enough items to make the limit of 200 items per
> --category go over so the .RecordCount property can be used to
> --warn us. But don't waste resources transferring excessive
> --amounts of data between tiers.
> SELECT TOP 201
> p.ItemNumber,
> Description
> FROM Products
> WHERE CategoryCode = @CategoryCode
> END
>
>

[Bob Barrows]

Ken VdB wrote:

> Create a stored procedure that selects any number of fields from any
> table. Use it to populate any kind of recordset other then "forward
> only" on the client and check your RecordCount property. Then
> declare any variable you like in the strored procedure and SET it to
> anything you like and watch your RecordCount property disapear
> (become -1). You don't even need to use the variable for anything
> else in the procedure. The data, table layout and particulars of
> exactly what the variable is used for for seem to be unimportant to
> this problem. I find it quite strange. I have attached some samples
> that use Pubs.
>

Use SET NOCOUNT ON at the start of the procedure, after the AS keyword.
HTH,
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]

My advice is to stop using an expensive non-forwardonly cursor merely to get
a record count. There are other more efficient ways to get a record count.
My favorite is to use GetRows to stuff the data from the recordset into an
array, allowing me to close and destroy the recordset and connection
(allowing another thread to use the connection). With the GetRows array, it
is a simple matter to get the count:
Ubound(arResults,2) + 1

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

[Ken VdB]

This does not help.

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:#MiXwyzlDHA.1072@TK2MSFTNGP09.phx.gbl...

> Ken VdB wrote:

> > Create a stored procedure that selects any number of fields from any
> > table. Use it to populate any kind of recordset other then "forward
> > only" on the client and check your RecordCount property. Then
> > declare any variable you like in the strored procedure and SET it to
> > anything you like and watch your RecordCount property disapear
> > (become -1). You don't even need to use the variable for anything
> > else in the procedure. The data, table layout and particulars of
> > exactly what the variable is used for for seem to be unimportant to
> > this problem. I find it quite strange. I have attached some samples
> > that use Pubs.
> >

> Use SET NOCOUNT ON at the start of the procedure, after the AS keyword.
> HTH,
> 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]

Ken VdB wrote:

> This does not help.

Hmm. I could not get a recordcount with your code even with the SET
statement commented out.

The only way I was able to get a static cursor (check the cursortype
property before attempting to read the recordcount) was to use a client-side
cursor. This is not the way it is supposed to behave. I am going to need to
come back and look at this later when I have more time.

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.

[Ken Schaefer]

Don't use the .RecordCount property. It's very expensive.

Instead:
a) Use .GetRows (this gives you the data in a VBSciprt array), and
UBound(arrResults, 2) + 1 gives you the recordcount

b) Use a SELECT COUNT() statement if you just need a count (no data)

Cheers
Ken


"Ken VdB" <puffthemagicdragon@nospam.floridus.com> wrote in message
news:uYH$hsylDHA.2820@TK2MSFTNGP10.phx.gbl...
: Hi everyone,
:
: I have noticed that if I used a stored procedure to populate an ADO
: RecordSet it only returns a .RecordCount property if that stored procedure
: contains nothing but a SELECT statement. For example, I am using the
: following stored procedure to get a list of items in a particular product
: category. If I eliminate the part that sets the @OrderDate to the current
: date, it returns a proper RecordCount. If I leave it in there (which I
need
: to do), I get (-1) ever time. This is a problem because then I cannot use
: the RecordCount property to warn the user that they are not seeing all
: items. Any thoughts are welcome.
:
: Cheers,
:
: Ken.
:
: CREATE PROCEDURE [dbo].[FL_KenTest]
: (@CategoryCode VARCHAR(5),
: @OrderDate SMALLDATETIME = NULL)
: AS
:
: BEGIN
: IF @OrderDate IS NULL
: SET @OrderDate = GETDATE()
:
: --Select just enough items to make the limit of 200 items per
: --category go over so the .RecordCount property can be used to
: --warn us. But don't waste resources transferring excessive
: --amounts of data between tiers.
: SELECT TOP 201
: p.ItemNumber,
: Description
: FROM Products
: WHERE CategoryCode = @CategoryCode
: END
:
:

[Bob Barrows]

Ken VdB wrote:

> This does not help.

Well, I've learned something here. I've never tried creating a server-side
scrollable recordset from a stored procedure. I guess it is not possible.
The only way I can get a non-forwardonly cursor from a stored procedure is
to use a client-side cursor. Ken, this applies to whether or not there is a
parameter whose value is set in the procedure.
..
Here is the code I've tried. If anyone can see something that I've failed to
do here, I'd be glad to hear about it.

<%
Option explicit
Dim strConn, strSQL
Dim conn, rsTest
dim cmd

strConn = "provider=sqloledb;data source=xxx;user id = xxx;" & _
"password=xxx;initial catalog=pubs"

Set conn = Server.CreateObject("ADODB.Connection")
Set rsTest = Server.CreateObject("ADODB.Recordset")

conn.open strConn
Response.Write "Parameter Optional" & "<BR><BR>"
strSQL = "Exec RecordCountProblemDemo"
'rsTest.CursorLocation=adUseClient
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using stored procedure executed via " & _
"dynamic sql, cursortype = " & _
rsTest.CursorType & "<BR>"
rsTest.Open strSQL, conn,,,adCmdText
Response.Write "After open, using stored procedure executed via " & _
"dynamic sql, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR>"

rsTest.Close
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using stored procedure executed via " & _
"procedure-as-connection-method, cursortype = " & _
rsTest.CursorType & "<BR>"
conn.RecordCountProblemDemo rsTest
Response.Write "After open, using stored procedure executed via " & _
"procedure-as-connection-method, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR>"

rsTest.Close
set cmd=Server.CreateObject("adodb.command")
With cmd
Set .ActiveConnection = conn
.CommandText = "RecordCountProblemDemo"
.CommandType = adCmdStoredProc
End With
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using stored procedure executed via " & _
"Command object, cursortype = " & _
rsTest.CursorType & "<BR>"
rsTest.Open cmd
Response.Write "After open, using stored procedure executed via " & _
"Command object, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR>"

rsTest.Close
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using dynamic sql, cursortype = " & _
rsTest.CursorType & "<BR>"
strSQL = "SELECT au_fname, au_lname FROM authors"
rsTest.Open strSQL, conn,,,adCmdText
Response.Write "After open, using dynamic sql, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR><BR>"

Response.Write "Parameter Required" & "<BR><BR>"

strSQL = "Exec RecordCountProblemDemoParmRequired 'test'"
'rsTest.CursorLocation=adUseClient
rsTest.Close
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using stored procedure executed via " & _
"dynamic sql, cursortype = " & _
rsTest.CursorType & "<BR>"
rsTest.Open strSQL, conn,,,adCmdText
Response.Write "After open, using stored procedure executed via " & _
"dynamic sql, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR>"


rsTest.Close
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using stored procedure executed via " & _
"procedure-as-connection-method, cursortype = " & _
rsTest.CursorType & "<BR>"
conn.RecordCountProblemDemoParmRequired "test",rsTest
Response.Write "After open, using stored procedure executed via " & _
"procedure-as-connection-method, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR>"

rsTest.Close
With cmd
Set .ActiveConnection = conn
.CommandText = "RecordCountProblemDemoParmRequired"
.CommandType = adCmdStoredProc
.Parameters.append .createparameter("@test",advarchar,adparaminput,11 , _
"test")
End With
rsTest.CursorType=adOpenStatic
Response.Write "Before open, using stored procedure executed via " & _
"Command object, cursortype = " & _
rsTest.CursorType & "<BR>"
rsTest.Open cmd
Response.Write "After open, using stored procedure executed via " & _
"Command object, cursor type = " & _
rsTest.CursorType & "; RecordCount = " & rsTest.RecordCount & "<BR><BR>"

rsTest.Close
conn.Close

Set rsTest = Nothing
Set conn = Nothing
%>

create PROCEDURE [dbo].[RecordCountProblemDemo]
(@SomeVarThatDoesntGetUsed VARCHAR(11) = NULL)
AS
BEGIN
SET NOCOUNT ON
--activate the following two lines and the RecordCount stops working
IF @SomeVarThatDoesntGetUsed IS NULL
SET @SomeVarThatDoesntGetUsed = 'hello world'

SELECT au_fname, au_lname FROM authors

END
GO
create PROCEDURE [dbo].[RecordCountProblemDemoParmRequired]
(@SomeVarThatDoesntGetUsed VARCHAR(11))
AS
BEGIN
SET NOCOUNT ON
--activate the following two lines and the RecordCount stops working
IF @SomeVarThatDoesntGetUsed IS NULL
SET @SomeVarThatDoesntGetUsed = 'hello world'

SELECT au_fname, au_lname FROM authors

END
GO


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.