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.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2005-04-15 : 11:24:46
|
| How can I convert below cursor to set based SQL statement?It does the job but take forever..------------------------------truncate table Aset nocount ondeclare @column varchar(4), @tsql varchar(250)declare c1 cursor forselect name from syscolumns where id = (select id from sysobjects where name = 'B') and name between '0' and 'A' open c1fetch next from c1 into @columnwhile (@@FETCH_STATUS = 0)beginset @tsql = 'insert into A select '''+@column+''' as ''category'',q_id, 'status_dt, org_id, p_id, sum(['+@column+']) as [count] from B with (nolock) group by status_dt, org_id, p_id, q_id, ['+@column+'] having sum(['+@column+']) > 0'exec (@tsql)fetch next from c1 into @columnend close c1deallocate c1 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-04-15 : 14:55:10
|
| Can you explain what you're doing?And it doesn't seem like you can do this with a set based opYou could probably lose the cursor though....but what the heck does this do?Brett8-) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-15 : 19:21:27
|
| First, it would be better to post the structure of the table you're working with. I suspect it is highly denormalized judging by what you appear to be trying to do. Secondly, you should also clearly describe WHAT you're trying to do, not how you're trying to do it.You also appear to have an improper use of the SUM function; as written, it will duplicate the column without actually summing it, unless you have duplicate rows.There is a way to write this without using a cursor, but we need the table structure first. Needless to say, we're still going to recommend redesigning this table so you don't have to do this crap to get the results you want. |
|
|
|
|
|
|
|
|
cursor to set based SQL statement