rowcount output from stored procedure

pokemon_john
pokemon_john used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

 

Author

Commented:
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.
Commented:
Oops..
Sorry my mistake.. The code I gave you was now to list the @@ROWCOUNT..because you said you were not able to get this working..

All the codes i've sent you should normally be run say..thru..query analyser for example..


But coming back to your problem... why dont you use the recordcount property..??

numberofrecords = rs.recordcount !!!

if you are using rs to display the elements... then use the recordcount property to get number of elements..!!!

Let us know how you are getting on..

Lichien
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

many thanks for this help.
john.

Commented:

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


Author

Commented:
Thankyou i have manged to make some sense and have got it working. thanks for your time
john
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial