SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with returning row count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allanurban
Starting Member

19 Posts

Posted - 07/31/2012 :  13:22:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/31/2012 :  13:43:19  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

allanurban
Starting Member

19 Posts

Posted - 07/31/2012 :  14:08:12  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/31/2012 :  14:16:37  Show Profile  Reply with Quote
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/

Go to Top of Page

allanurban
Starting Member

19 Posts

Posted - 07/31/2012 :  14:34:08  Show Profile  Reply with Quote
That sounds like exactly what I need.

Just one final question then. How do I capture the result in a variable?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/31/2012 :  14:55:23  Show Profile  Reply with Quote
SELECT @Variable= COUNT(*) FROM table

Make sure you declare it before using


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

Go to Top of Page

allanurban
Starting Member

19 Posts

Posted - 07/31/2012 :  15:02:13  Show Profile  Reply with Quote
Thanks a bunch! Works like a charm.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/31/2012 :  15:15:40  Show Profile  Reply with Quote
ok...np

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

Go to Top of Page

allanurban
Starting Member

19 Posts

Posted - 12/20/2012 :  08:04:40  Show Profile  Reply with Quote
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 :-)

Edited by - allanurban on 12/20/2012 08:05:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/20/2012 :  08:10:05  Show Profile  Reply with Quote
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/

Go to Top of Page

allanurban
Starting Member

19 Posts

Posted - 12/20/2012 :  08:46:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/20/2012 :  11:28:20  Show Profile  Reply with Quote

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


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000