asked on

rowcount output from stored procedure

Hello
i have a stored procedure which displays a recordset of data in an ASP page.
CREATE PROCEDURE ListRecords
(
@fromdate varchar(15),
@untildate varchar(15)
)
AS

declare @sql varchar(1024) --local var for sql string@
set @sql = 'select ImageID,ImageName,Metkeyword, MetsubjectArea,Picturl, convert(varchar,[date submitted],20) as "date_sub"
from imagebankmeta1
where [date submitted] > '''+@fromdate+'''
AND [date submitted]< '''+ @untildate + ''''

exec (@sql)
GO

What i would also like to do is know the number of records that this query returns. I know of @@ROWCOUNT, but have not been able to get it working with my query.
Is it possible to have another output from this stored procedure to my ASP page, which calls the sql by

sql = "{call dbo.ListRecords('" + fromDateStr + "','" + untilDateStr +"')}"
rs.open sql

I then use rs to display elements fromt he recordset.
many thanks
john.

Lichien

john,

if you get your sp to have an output parameter..

Create procedure listrecords
@fromdate varchar(15),
@untildate varchar(15),
@retvalue varchar(15) OUTPUT
As
--- your codes here ---

exec(@sql)
select @retvalue = @@rowcount
GO

Now to execute the stored procedure..

declare @returnval varchar(15)
execute listerecords (--parameters go here --, @returnval OUTPUT)
if @returnval >0
select @returnval

this will list values return by @sql

and then the number of rows.

Hope this helps..

Lichien

pokemon_john

ASKER

Thanks but i'm still confused.
I tried your suggestion but where i had to place the @returnval in the ASP page, it did not recognise this, i tried variations but to no hope.
thanks
john.

ASKER CERTIFIED SOLUTION

Lichien

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pokemon_john

ASKER

Thanks for that.
Although it only returns a value of -1

many thanks for this help.
john.

Lichien

Recordcount returns -1 in some cases..
you sometimes have to issue a movelast before checking recordcount

Check this link it maybe helpful

http://support.microsoft.com/default.aspx?scid=kb;en-us;194973

Cheers
Lichien

pokemon_john

ASKER

Thankyou i have manged to make some sense and have got it working. thanks for your time
john

CleanupPing

pokemon_john:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.