[thelist] returning the equiv. of rs.RecordCount with a stored procedure? (SQL7)

Howard Cheng howcheng at ix.netcom.com
Tue Mar 5 13:27:02 CST 2002


I would say you have two ways to do it. One is to use an output parameter
and do two select statements, the first being a "SELECT COUNT(*) FROM ...".
The other way would be to do one statement using a UNION clause -- the
first row would be your SELECT COUNT.

Method 1:

CREATE PROCEDURE myProcedure (
         @mycount INT OUTPUT
)
AS
         SELECT  @mycount = COUNT(*)
         FROM    ...
         WHERE   ...

         ' second select statement here
RETURN

Method 2:

CREATE PROCEDURE myProcedure AS

         SELECT  COUNT(*) AS mycount, null AS field1, null AS field2
         FROM    ...
         WHERE   ...

         UNION

         SELECT  null AS mycount, field1, field2
         FROM    ...
         WHERE   ...

RETURN

At 01:58 PM 3/5/2002 -0500, Chris Blessing wrote:
>That's what I'm trying to do anyhow; up until now I've had all my sql
>statements that I need a RecordCount for executing from within the ASP page
>itself, as opposed to being a stored procedure in SQL server.
>
>Any ideas on how to get a recordcount AND a recordset returned from an sp?
>Usually I do something like this with stored procs:
>
>set rs = conn.execute("usp_mysp [params...]")
>
>Thanks!

::::::::::::::::::::::
Howard Cheng
howcheng at ix.netcom.com
AIM: bennyphoebe
ICQ: 47319315




More information about the thelist mailing list