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 OUTPUTASBEGIN 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.trydeclare @i intexec proc @i outselect @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 @@ROWCOUNTuse SELECT COUNT(*) FROM tableand capture result in a variable which you can return along with resultset if you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 tableMake sure you declare it before using------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 useSELECT productID, quantity, SUM(quantity) OVER (PARTITION BY productID) AS totalquantityFROM orders if below sql 2005 useSELECT t.productID, quantity, totalquantityFROM orders tINNER JOIN( SELECT productID, SUM(quantity) AS 'totalquantity' FROM orders GROUP BY productId ) t1ON t1.productId = t.productId ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 SeqFROM orders)tWHERE seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|