| Author |
Topic |
|
phaze
Starting Member
42 Posts |
 Posted - 2005-03-15 : 18:02:44
|
| I have the following stored procedure:create procedure COB_GetMotiveResults@motive varchar(10)asselect account, amount, motivefrom COB.dbo.transwhere motive = @motiveWhat would I need to do in order to include a cursor? How would it look like? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-15 : 18:05:07
|
| So you didn't see X002548's cursor example in the other thread then?Is this for a class?Tara |
 |
|
|
phaze
Starting Member
42 Posts |
Posted - 2005-03-15 : 18:07:39
|
Tara, you've been busting my balls all day. LOL no this is not for class. My work asked me to learn this so I wanted to asked experienced people on here to give me an example. That is all... no need to get fiesty  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-15 : 18:10:55
|
| Experienced people don't use cursors, so we'll just keep referring you to Brett's example or the examples in SQL Server Books Online. Did his example not show you enough? Do you know where SQL Server Books Online is? It's the documentation for SQL Server that gets installed when you install the SQL client tools.Tara |
|
|
|
phaze
Starting Member
42 Posts |
Posted - 2005-03-15 : 18:13:40
|
| Well being the NOOB that I am, some of the things weren't that clear. I am still in the learning process of SQL. So I figured that once I created my stored procedure maybe someone could guide me to the right way of including a cursor in it. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-15 : 18:58:32
|
| Tell your boss you learned this: "never use cursors except as a last resort." That should be enough. And, in general, read books on-line and experiment using Northwind. Nothing beats experimenting and seeing what you can do, it is much more useful than asking very broad questions.- Jeff |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-03-15 : 20:36:00
|
| I'm curious: do you guys feel that set-based transactions should always be used instead of cursors, or that a WHILE loop containing set-based transactions should be used instead of cursors? For example, if one is doing a complex data conversion from one table to another, what do you think is the best way to handle it? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-15 : 21:37:42
|
| Without cursors. |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-03-16 : 00:09:56
|
| OK, let's say you are doing a data conversion from a source where you have no control over the data. A set based transaction (like INSERT ... SELECT ...) will insert zero records if there are any unexpected data anomalies. It is extremely difficult to anticipate all possible anomalies, so I would think that a cursor would be better because it would at least insert all the other records. In an environment where conversion speed is not critical, a production table that is only missing a few records is better than one that is completely empty. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-16 : 02:33:39
|
| Ok, nosepicker.What do You think, does your scenario justify a cursor?Just think a little about the problem and try to come up with a yes/no answer.How would the corsor let you handle "all possible anomalies" and the setbased approach would not?phaze: read the manual, and try some, come back with more specific questions if you get stuck.rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 05:51:09
|
ok some pep-talk to phaze you'llmaybe need cursors in 0.01% of the time.in that 0.01% depending on the table size you'll be off using a loop.why is everyone against cursors? because we've seen too many examples on this site when newbies see cursors see it's a row by row processing. because that concept is more easily grasped than set based thinking they stick with it.so i say that yes, you should learn about cursors. but also learn why not to use them. you'll be way better of without them. and besides you'll get a huge ego boost when you tell someone that he shouldn't use them when you know why not Go with the flow & have fun! Else fight the flow  |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-03-16 : 12:51:19
|
| Rockmoose: I didn't say that cursors would handle all data anomalies better than a set-based transaction. I said that an occurrence of a data anomaly in a cursor would generally only cause that one record to not be inserted, whereas with a set-based transaction the entire recordset would fail to be inserted. If conversion speed is not critical, then it's better to have a table missing one record than missing all records. In a production environment, sometimes speed has to be sacrificed for data availability and data accuracy, especially if you have no control over the data source. This may be that 0.01% situation for others, but it's something I encountered all the time in my previous job.I completely agree that cursors should be avoided if possible, especially if speed is an issue. But people talk about them as if they were poison, and I just feel that there are certain situations where they are beneficial. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-16 : 12:53:54
|
| In these situations, I use a WHILE loop instead of a cursor.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 13:06:54
|
| In a staging table I would have a column "IsOK" and I would flag that column (using UPDATE) for all know duff-data scenarios (Intednded numeric column is not numeric, etc.)Then I would do an INSERT TargetTable SELECT Col1, Col2, ... FROM StagingTable WHERE IsOK = 1If you have a cursor and a T_SQL loop trying to store a TextString into a NumericColumn is going to generate a non-trapable error, isn't it?Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 13:40:32
|
quote:
I said that an occurrence of a data anomaly in a cursor would generally only cause that one record to not be inserted
This is not necessarily a good thing.quote:
whereas with a set-based transaction the entire recordset would fail to be inserted. If conversion speed is not critical, then it's better to have a table missing one record than missing all records
No it isn't. If you are loading multiple tables that require referential integrity between them, one missing row in any of them violates this integrity. This isn't just theory; if you deal with financial or other monetary data, you cannot just assume it's OK to skip a row here and there and figure it out later. Even in a situation where it IS acceptable, it's a poor approach/attitude to take towards the data. Regardless, there are ways to deal with this issue that do not require cursors (staging tables, data cleansing, etc.)quote:
In a production environment, sometimes speed has to be sacrificed for data availability and data accuracy, especially if you have no control over the data source.
I'm trying to understand how you could say that AND suggest it's OK to skip rows in a data import. Still, it has nothing to do with cursors, except that cursors would make it easier to violate the integrity of the data.quote:
I completely agree that cursors should be avoided if possible...But people talk about them as if they were poison
I agree with both statements. The problem is not just cursors, it is also the MINDSET that would justify when they are acceptable. THAT is poison. So are cursors, really. Once someone finds one scenario where a cursor makes sense, or is just easier to work with, they spread like viruses throughout everything. Browse these forums for 20 minutes and you'll find more examples than you can count.If that's not enough, I can post a couple hundred stored procedures from my job where someone felt a cursor was a good idea. I can use all the help I can get in cleaning them up. Hopefully it will happen before we lose another client because we can't process their data in a timely enough manner. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 13:45:55
|
| i have some time on my hands right now. i'll just charge $150 per hour for consultancy.... Go with the flow & have fun! Else fight the flow |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-03-16 : 14:08:59
|
| Kristen: you present a good point and solution. I've encountered some extremely complex data conversions in the past that would seem to make your suggestion difficult to apply, but I will keep your solution in mind and make some performance comparisons.robvolk: I agree that missing one record is not the ideal situation to have. As a fellow DBA and DB developer, I like everyone always strive for 100% accuracy 24/7/365. There are certain types of applications and situations that I encountered in the past where 99.99% accuracy was a heck of a lot better than zero data. At least the client could do most of their work while we fixed the data anomaly.I also agree that the default mindset of constantly using cursors is bad thinking. I feel that as long as one understands how cursors work and how set-based transactions work, one can have options in finding a solution that best fits their environment, because everyone's is different.Thanks to everyone for engaging in an educational and non-antagonistic discussion. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-16 : 14:38:43
|
quote:
Originally posted by tduggan
In these situations, I use a WHILE loop instead of a cursor.Tara
In these situations, I load all the data to a staging table and audit all the data.Now depending on the business rules, I either exclude then do the insert, or send an email to the person responsible for the data source and say "clean up your f'n data"No cursor there.And why didn't you try my example, all you have to do is copy and paste it in to QA and SEE how it works...I was giving you enough rope...but even then...sheeshBrett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-16 : 14:43:54
|
quote:
Originally posted by nosepicker
Thanks to everyone for engaging in an educational and non-antagonistic discussion.
Your welcome.But, one would get put off if you waste your time with a sample that's not even looked or used, and what was exactly asked for, then the same question was asked again....http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47120Brett8-) |
|
|
|
phaze
Starting Member
42 Posts |
Posted - 2005-03-16 : 16:59:59
|
| brett, i copied and pasted your query into my QA and it gave me a few errors. |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-03-16 : 17:05:04
|
| Generally, when reporting 'errors', it is useful to state the actual text of the error so as not to waste people's time guessing what went wrong. |
|
|
|
Next Page
|
using cursor