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 |
|
IndyGiGi
Starting Member
1 Post |
 Posted - 2013-05-06 : 10:51:30
|
| Everyone keeps telling me not to use cursors, that there is always something you can do besides a cursor. But what if I have to cycle through each line of returned data? What would I use? For example, say I want to return a chunk of data for a bunch of different people. Each of these people has 3 effective dates, but I only want to get the earliest effective date. How would I do this if not with a cursor? Thanks! |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-05-06 : 11:07:00
|
Well in most cases, its possible to acheive the result without using cursor, but sometimes there might be cases when you have no choice except to use a Cursor. The reason for avoiding cursor is to perform an operation in best possible way (in terms of performance). So, post sample data in consumable format (meaning that in the form of insert statements) and the desired ouput for that sample data. The team will come up with solution(s), if there is a way to get it solved without using a cursor. :)quote:
Each of these people has 3 effective dates, but I only want to get the earliest effective date. How would I do this if not with a cursor?
I am thinking that this could be done with the help of Row_Number function.CheersMIK |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-06 : 12:31:53
|
quote:
Originally posted by IndyGiGi
Everyone keeps telling me not to use cursors, that there is always something you can do besides a cursor. But what if I have to cycle through each line of returned data? What would I use? For example, say I want to return a chunk of data for a bunch of different people. Each of these people has 3 effective dates, but I only want to get the earliest effective date. How would I do this if not with a cursor? Thanks!
In general cursors are bad because they are not memory-safe. But, they, like everything else are a tool and, if used, need to be used appropriately. There are many ways to do what you are asking about without a cursor. I'm guessing that you are not thinking in a set-based fashion so you are only thinking of the iterative solution. So, as Mik suggests post sample data and expected output and we can help. Here are some links that can hel you prepare that:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2013-05-07 : 13:57:02
|
| regarding cursors, I never use them in t-sql, but I've noticed they are used frequently in pl/sql. Does anyone know why they are looked down upon in t-sql but embraced in oracle? Just wondering. The only time i've ever used any kind of cursor is when I had to excucute a stored procedure on each individual row to send an email, but other than that, no use for it. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-05-07 : 18:47:26
|
PL/SQL and Oracle have historically worked a lot closer to the metal than SQL Server, so their cursor implementation is more efficient. SQL Server has focused more on having the query optimizer generate the physical operations rather than requiring the user to specify them.Conceptually though, set-based processing is typically a lot easier to write and the code is usually smaller. For instance, in the original request about earliest effective date:SELECT UserName, MIN(EffectiveDate) EarliestFROM myTableGROUP BY UserName Any enumeration through the results would be done on the application side rather than a server-side cursor. |
|
|
|
|
|
|
|
|
Cursors