Problem with returning row count

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

General SQL Server Forums

New to SQL Server Programming

Problem with returning row count

Author

Topic

allanurban
Starting Member

21 Posts

Posted - 2012-07-31 : 13:22:27

My goal is to get the data from col1 and col2 and it works fine with the below. I also want to return the row count, but for some reason I cant get it to work. Is there some obvious error in the below code?


ALTER PROCEDURE proc
	@returnValue INT OUTPUT
AS
BEGIN
	SELECT col1
	      ,col2
	  FROM tabel
	;
	SELECT @returnValue = @@ROWCOUNT;
	RETURN @returnValue;
END;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-31 : 13:43:19

You don't need the return statement as this is an output variable (if that's what you want).
It might not be available in the client until the resultset is closed.

try
declare @i int
exec proc @i out
select @i

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

allanurban
Starting Member

21 Posts

Posted - 2012-07-31 : 14:08:12

Yeah that fixed it. I simply needed to close the resultset first. Thanks.

Not sure I understand the last part though. Should I declare the returnValue after the first query instead?

Also, the first solution gives me another problem. I need the rowcount before i start processing the data, so while closing the resultset gives me the count, I lose the data. I can make it work by reloading the data after having closed the resultset the first time, but thats just ugly, and I'm sure it is not needed. I just dont know how to get around it :-P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 14:16:37

quote:
Originally posted by allanurban

Yeah that fixed it. I simply needed to close the resultset first. Thanks.

Not sure I understand the last part though. Should I declare the returnValue after the first query instead?

Also, the first solution gives me another problem. I need the rowcount before i start processing the data, so while closing the resultset gives me the count, I lose the data. I can make it work by reloading the data after having closed the resultset the first time, but thats just ugly, and I'm sure it is not needed. I just dont know how to get around it :-P

then rather than using @@ROWCOUNT

use

SELECT COUNT(*) FROM table

and capture result in a variable which you can return along with resultset if you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allanurban
Starting Member

21 Posts

Posted - 2012-07-31 : 14:34:08

That sounds like exactly what I need.

Just one final question then. How do I capture the result in a variable?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 14:55:23

SELECT @Variable= COUNT(*) FROM table

Make sure you declare it before using

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allanurban
Starting Member

21 Posts

Posted - 2012-07-31 : 15:02:13

Thanks a bunch! Works like a charm.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 15:15:40

ok...np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allanurban
Starting Member

21 Posts

Posted - 2012-12-20 : 08:04:40

I have another problem which is very much related to the above.

Again, I want to return a rowcount along with a recordset. I just to do it as another column.

Basically I have a order table with all orders. I then want to return the sum of all sales for each product Id.

 SELECT productID, quantity, COUNT(*) AS 'count'
	FROM (
		SELECT productID, SUM(quantity) AS 'quantity'
		FROM orders
		GROUP BY productId
		) AS tabel

Above wants me to group by productId and quantity, but then the count just returns 1 for each line, and not the actual number of rows from the inner select. Im sure the solution is easy, I just don't know it :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 08:10:05

if sql 2005 or above use


SELECT productID, quantity, SUM(quantity) OVER (PARTITION BY productID) AS totalquantity
FROM orders

if below sql 2005 use


SELECT t.productID, quantity, totalquantity
FROM orders t
INNER JOIN(
		SELECT productID, SUM(quantity) AS 'totalquantity'
		FROM orders
		GROUP BY productId
		) t1
ON t1.productId = t.productId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allanurban
Starting Member

21 Posts

Posted - 2012-12-20 : 08:46:02

Thanks for the reply.

I have probably not explained myself good enough. I need the number of rows in the recordset and not the total qty.

Below works, but it looks ugly...


		DECLARE @count INTEGER
		SELECT @count = COUNT(*)
		FROM (
		SELECT productID, SUM(quantity) AS 'quantity'
		FROM orders
		GROUP BY productId
		) AS tabel


		SELECT productID, SUM(quantity) AS 'quantity', @count AS 'count'
		FROM orders
		GROUP BY productId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 11:28:20

[code]
SELECT productID,totalquantity,SUM(1) OVER () AS [count]
FROM
(
SELECT productID, SUM(quantity) OVER (PARTITION BY productID) AS totalquantity,ROW_NUMBER() OVER (PARTITION BY productID ORDER BY quantity) AS Seq
FROM orders
)t
WHERE seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources