Re: Best way to count records returned?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/04/04 

Date: Sat, 4 Sep 2004 10:07:26 -0500

IIRC, ADO returns OUTPUT parameters only after results are retrieved so you
need to first process the returned recordset. Also, include SET NOCOUNT ON
at the beginning of the proc to suppress DONE_IN_PROC messages that can
interfere with ADO resultset processing. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Lewis - Website Nation" <user@domain.com> wrote in message 
news:6MWdncMb2t6v5qTcRVn-vA@comcast.com...
> What is the best way to count the number of records returned in a stored 
> proc.?
>
> I have tried using @@ROWCOUNT and assigning that to an Output parameter, 
> but that has not worked. I then researched and found others saying that I 
> should use count() because using @@RowCount is redundant. What is the best 
> way?
>
> I am using ASP, ADO and SQL Server 2000. I just want to display the number 
> of records returned.
>
> So far I have the following. It returns the records, but not the count:
>
> CREATE PROCEDURE prViewMemberImageUpload
> @MemberID int, @RecordNum int OUTPUT
>
> AS
> SELECT
> ImageNew, ImageOriginalFilename
> FROM
> tblImage
> WHERE
> MemberID = @MemberID
>
> Set @RecordNum = @@Rowcount
> Return @RecordNum
> GO
>
>
>
> -- 
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com

Relevant Pages

  • Re: SQL Server stored prcedures with output parameters
    ... >>Unfortunately as far as I can see the DB API docs are silent on how to ... >>distinguish the output parameters to a callproc, ... >>the input sequence. ... Erm, I was trying to be DB API compatible in Python, so the ADO stuff ...
    (comp.lang.python)
  • Re: SQL Server stored prcedures with output parameters
    ... > Unfortunately as far as I can see the DB API docs are silent on how to ... > distinguish the output parameters to a callproc, ... > the input sequence. ... My ADO is a little rusty, ...
    (comp.lang.python)
  • RETURN_VALUE????
    ... I am working with ADO in SQL Server 2000 using a ADO ... I see via Refresh that the first parameter is no longer what ... I can collect the output parameters no problem but my question is ...
    (microsoft.public.data.ado)
  • Accessing ADO output parameters from SQL Server to VC++.net
    ... I have a stored procedure in SQL Server database with one input and one ... output parameters. ... ADO, but cannot access the output value. ...
    (microsoft.public.dotnet.framework.adonet)
  • Accessing ADO output parameters from SQL Server to VC++
    ... I have a stored procedure in SQL Server database with one input and one ... output parameters. ... ADO, but cannot access the output value. ...
    (microsoft.public.data.ado)