Add a column in SQL query that increments when using a cursor

Question

Using SQL server 2012.

I am trying to rank some results using a cursor but having problems.

The code is as follows.

declare @companyid int
declare @tempTable table(rank int, companyname nvarchar(50), hedgevalue float)
declare c cursor for select id from gascompanies

open c

fetch next from c into @companyID

while @@FETCH_STATUS=0 begin
--insert into @tempTable([rank], companyName, hedgeValue) 
select rank=count(*), gc.companyName as companyName, hedgevalue
from DayAheadSettings DAS
left outer join GasCompanies GC ON gc.ID = das.CompanyID
left outer join gashedges on gc.id = GasHedges.CompanyID
where nominatehedge = 0 and gc.ID = @companyid and hedgeDate = convert(date, GETDATE() + 1)
group by CompanyName, hedgevalue

fetch next from c into @companyid
end
close c
deallocate c

This produces the following results as there are 3 company ID's

result set

The table above has the rank of 1 twice and I would like RWET to be 1 and JPMorgan 2 and if there was a third then 3. (ideally I wouldn't want the third result to show unless there is data)

If I ran the query and there was only one with data, JPMorgan for example then I would like the rank to be 1.

Hope that all makes sense.

How about not using the cursor at all? There is no need to use a cursor for inserts like this. You can use ROW_NUMBER for this and drop the cursor. — Sean Lange, Sep 17 '14 at 16:52
but how do I run it for each company id from the gascompanies table — Silentbob, Sep 17 '14 at 16:52
how about some ddl and sample data? sqlfiddle.com is a great place to start. — Sean Lange, Sep 17 '14 at 17:06
Can you please share the schema for GasCompanies, gashedges and DayAheadSettings tables. That helps in simulate the issue in our sand boxes. — Bhasyakarulu Kottakota, Sep 17 '14 at 22:39
Please unlearn the cursor-curse method. Whoever inherits your code will thank you unceasingly. — granadaCoder, Sep 18 '14 at 15:10
https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/ — granadaCoder, Sep 18 '14 at 16:33
http://www.sqlservercentral.com/Forums/Topic642789-338-1.aspx — granadaCoder, Sep 18 '14 at 16:34
http://stackoverflow.com/questions/1687512/rbar-vs-set-based-programming-for-sql — granadaCoder, Sep 18 '14 at 16:35

score 0 · Answer 1 · answered Sep 18 '14 at 15:03

0

After looking at this for sometime I had a epiphany I changed the rank to an ID column that auto incremented.

This solved my issue.

answered Sep 18 '14 at 15:03

Silentbob

2,805
7
38
70

You're still missing the boat dude. Please stop writing cursors. – granadaCoder Sep 18 '14 at 15:12
what would be an alternative if I need to do what is essentially a For Each loop through the ID's in another table. – Silentbob Sep 18 '14 at 16:29
Post your DDL, some sample inserts and expected results. "SetBased" thinking does not have a "for-each" mentality. – granadaCoder Sep 18 '14 at 16:32

Add a column in SQL query that increments when using a cursor

1 Answers1