| Author |
 Topic  |
|
|
allanurban
Starting Member
17 Posts |
 Posted - 07/31/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/31/2012 : 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
17 Posts |
Posted - 07/31/2012 : 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
India
47099 Posts |
Posted - 07/31/2012 : 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
17 Posts |
Posted - 07/31/2012 : 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
India
47099 Posts |
Posted - 07/31/2012 : 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
17 Posts |
Posted - 07/31/2012 : 15:02:13
|
| Thanks a bunch! Works like a charm. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/31/2012 : 15:15:40
|
ok...np
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
allanurban
Starting Member
17 Posts |
Posted - 12/20/2012 : 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 :-) |
Edited by - allanurban on 12/20/2012 08:05:30 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 12/20/2012 : 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
17 Posts |
Posted - 12/20/2012 : 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
India
47099 Posts |
Posted - 12/20/2012 : 11:28:20
|
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/
|
|
|
| |
Topic |
|
Problem with returning row count
