Getting RecordCount in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting RecordCount in Stored Procedure

Author

Topic

edb2003
Yak Posting Veteran

66 Posts

Posted - 2003-09-11 : 16:00:21

Hello,
I have this stored procedure below.

CREATE PROCEDURE usp_GetCustomers AS
SELECT Customer_id, customer
FROM dbo.Customer
ORDER BY customer
Go

Can someone tell me what the syntax is to grab the number of records in the table so that I can add it into this stored procedure and call for it in my online form. I can go into more details of why I need the record count but was just wondering if it can be done in my stored procedure.

thx,
Ed

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 16:13:53

DECLARE @x int
SELECT @x=COUNT(*)
FROM dbo.Customer
ORDER BY customer
Return @x

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

tacket
Starting Member

47 Posts

Posted - 2003-09-11 : 17:32:47

I don't think that last post will work. Mabey I'm missing something here, but you can't have an order by clause with the count(*) unless you have a group by clause as well.

CREATE PROCEDURE usp_GetCustomers AS
SELECT Customer_id, customer, count(*) as Rowcount
FROM dbo.Customer
group by Customer_id, customer
ORDER BY customer
Go

I'm not sure you can store that count(*) in a variable either when you have the customer_ID and customer with it.

You could say:
CREATE PROCEDURE usp_GetCustomers
@X int output
AS
SELECT @x = count(*)
FROM dbo.customer

return @X

/*
--call it using this:
declare @blah int,
@x int

exec @blah = usp_GetCustomers @X = @Blah OUTPUT

select @Blah
*/

Hope that helps.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-11 : 17:42:32

If you open the recordset in your app there is most likely a RecordCount property that can be accessed.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-09-11 : 19:00:39

Another option is @@RowCount


CREATE PROCEDURE usp_GetCustomers(
@RowCount INT OUTPUT
)
AS
SELECT Customer_id, customer
FROM dbo.Customer
ORDER BY customer

SELECT @RowCount = @@RowCount
Go

So, your stored proc would return a "recordset" and an Output parameter. I think Brett's solution is best.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-11 : 19:18:21

quote:
Originally posted by tacket

I'm not sure you can store that count(*) in a variable either when you have the customer_ID and customer with it.

That is correct. You can not combine data retrieval and putting values into variables in the same statement.

@@ROWCOUNT is the way to go for this as Michael mentioned.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-12 : 10:51:34

My bad....

That's what I get for cutting and pasting...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources