Cursors
-
OK, so I posted this yesterday but it never showed up so I'll try again...
I appreciate the feedback I've gotten on my own set vs. cursor problem, but I haven't been able to apply the suggestions to my particular problem so I thought I'd restate it and see if anyone has a good set-based answer.
I have a Stored Procedure that gets two parameters, a ProjectID (INT) (i.e. 116) and a CSV list of KeyID's (VARCHAR) (i.e. '11,23,47'). I use a function I got here in the script library to turn the CSV string into a table of integers. I now need to insert a row into another table for each entry in the table of integers where the fields specified in the Insert are the ProjectID parameter and the KeyID from the table. The table of integers built by the function from the CSV looks like this:
- ID KeyID
- 1 11
- 2 23
- 3 47
The ID field above is an INT Identity field. The resulting destination table after the inserts should look like this:
- PKey ProjectID KeyID
- ..... rows existing prior to Insert(s)
- 7 116 11
- 8 116 23
- 9 116 47
I'm looking for a good set-based way to do these inserts or it's off to cursor land I go.
TIA
-
Let's start off with your tone: "Let's start off with your **REAL** problem; you don't know what First Normal Form (1NF) is and someone let you get on a database. That is probably why you thought of cursors."
Starting your response off with an insult casts doubt on the remainder of your suggestion. Why would I take advice from someone who first tells me I don't know what I'm doing?
With regard to your "solution", you posted this code earlier in this thread. I posted a response indicating a problem with part of the code as posted. Maybe it was "translating it into local dialect", but you never responded to my specific question. My second post contained very specific code and data, and I was hoping for a solution that addressed specifically what I posted, not a generalized "foo" and "foobar" response that as far as I can tell doesn't really address my problem.
Not all of us have the luxury to spend all our time fully normalizing databases. We inherit applications developed by others. From our customers' perspective the application works. They don't care if the database is 1NF, 2NF or 3NF, they just want it to work. They're not going to pay me to normalize the database if there's no business advantage to them to do so. Therefore I have to make things work within the framework of what I've inherited. Thus the problem I'm trying to solve here.
You don't always know all the conditions involved or reasons people do things they way they do. Sometimes they don't have much choice. I've looked at a number of your posts and they have a condescending and, in some cases such as mine, insulting tone. You might want to think about how you word your responses. Someday you may want help and the people you've been looking down on from your perch may be loath to offer given they way you talk down to them.
-
It says look at a list and returnthe first non-NULL value, casting it to the highest datatype of the expressions in the list. It is a member of the CASE expression family.
...and as such suboptimal implemented in SQL Server and/or in BOL described.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hm, why is everybody picking on Joe? Maybe because he is who he is? Right now I guess almost everybody here knows who Joe Celko is and also knows his "style". Agree with him or not, but are people really getting upset on his tone or language? I can't believe this. And actually to get really flamed and blasted ask on Java or C++ lists what a class is. I guarantee you'll learn a few new words
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I've posted my share of both questions and answers in these forums. Joe can have his style, but he doesn't get to tell me I don't know what I'm doing based on a simple question. He doesn't know me. He doesn't know the entire situation. He made a judgement about my knowledge and abilities based on a simple question posted here. It's simply a question of manners. If he's going to form poorly founded opinions about people based on a couple of sentences in a post and then post them publicly perhaps he should not post at all. There are plenty of people available here to answer questions.
Beyond that, as far as I can tell, he didn't answer my question. In the meanwhile I came up with my own set-based approach that is half the code he posted simply because it is directed at the specific problem I posted. I posted as much detail as possible to avoid the generic statements and see if someone had a specific solution to a specific problem. Joe posted a very generic piece of code full of "foo" and "bar" and not responding in any way to the very specific question I asked. Where's the value in that?
I post answers to a lot of what I consider very basic questions. Answers anyone who's been given access to a SQL Server should already know and not have to ask. The posts have been direct and polite. I've never taken a tone with anyone. As the saying goes: There's no such thing as a stupid question.
Hopefully this is the end of this line in the thread.
-
Hopefully this is the end of this line in the thread.
Did you really think so?
My whole point was that there is not built in automatism, that one need to get upset, because he receives a reply by Joe Celko. Why are people apparently not able to see things a bit more relaxed? Calm down, drink a tea, and rather force Joe to get into details and explanations rather than simply replying in anger. That way we all get the most from his knowledge and have fun.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Well,
My point is that it isn't necessary to make personal comments. I don't care what someone's "style" is, it's rude. Imagine the fights we'd have here if everyone started their replies with "Obviously you don't know what you're doing..."!
As to your comment that I should "force" Joe to give me more detail, etc, if you look at the entire thread Joe posted the exact same solution earlier in the thread (minus the insult) in response to my initial post. I had a problem with part of his SQL and posted a reply asking for more information. He never responded. I assumed the lack of any response, from Joe or otherwise, was because the description of the problem wasn't clear enough. I posted a more detailed description of the problem, hoping for a clearer and more to the point response, whether from Joe or someone else. What I got was the exact same response from Joe, but prefaced with a personal insult. So in addition to insulting me, Joe provided no additional help.
I didn't get upset because I received a reply from Joe Celko. I got upset because I was publicly insulted. I would have reacted similarly to anyone posting such a personal comment in any public forum.
I'm not the one with the problem, Joe is the one with the problem if he finds it impossible to answer people's questions in a civil manner without stooping to unfounded comments about people's knowledge or experience.
-
Joe is just out right rude in his reply. I see it as a sign of insecurity.
"Let's make others feel stupid so I can feel smarter."
That's how you sound, Joe. At least, that's how I took your reply. Anyone who starts of a response in the manner he did exemplifies his/her need to justify his/her own self worth. And, like rschaeferhig, I stopped reading your reply. I don't have time to read ignorance.
Regarding the matter at hand which started this thread, comparing a cursor to a set query is like comparing a crescent wrench to a rachet. You can fasten a nut to a bolt quicker using a rachet than a crescent. So, of course, in general you would normally use the rachet. However, ever try using a rachet set on a nut buried under a carburator? It's too hard to get at. Instead, a crescent makes more sense and will do the job better.
I have used cursors in applications. I have been developing enterprise level client/server applications for nearly 20 years. I am confident in what I am doing and know when to use them. My earlier post shows an example. I cannot give more detail because the algorithm I use is proprietary. To say you never should use them is like saying there is no need for a crescent wrench anymore since a rachet is so much quicker. If you are using a cursor where a set query will do, of course you need to reconsider. However, do not make the mistake of thinking cursors do not have a place in SQL programming. It would have been deprecated by now.
-
By the way, John. Where are you? You posted only twice to this thread? You started this fiasco and have not done a good job of moderating the conversation.
Stay in the loop, bud.
You point has not been made yet.
-
My point was made earlier, the questions I posed were not responded to, and the discussion digressed into threads of "what is coalesce", and some flaming...
(and I got really busy last week)
I thought it best not to get involved. I didn't think there was enough interest being displayed in the real issue, or fundamentals... oh well...
The issues may all be moot anyway with microsoft continuing to muddy the relational environment with its "enhancements" that will only further enable procedural thinking (and slow down applications---job security I guess, sigh).
-
The issues may all be moot anyway with microsoft continuing to muddy the relational environment with its "enhancements" that will only further enable procedural thinking (and slow down applications---job security I guess, sigh).
Am I reading you right, that you can't wait for SQL Server 2005 and the implementation of the CLR?
Curious (hm, wait,...not really) what kind of question then will show up here. I guess questions about cursors will then be of the better ones
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I attended a webcast overview on SS 2005 and they talked very briefly about TSQL vs. CLR. They don't expect a mass migration to CLR. The presenter's take (he was from MS) was that data access would stay in TSQL. The only time you'd go to CLR is if you really needed non-database procedural code, then you'd write that in CLR and use it as a wrapper for TSQL data access stored procedures and functions.
At least that's what I thought I heard him say... If so it's an interesting take. They have put a lot of effort into improving TSQL (recursive queries, try/catch, extended error handling, PIVOT...)
Probably still to early to tell but based on what I saw/heard yesterday I think he may be right.
-
ayecarumba... they are going to spread the black plague of try/catch error handling to TSQL... yikes!
This is the "I'm too lazy to write good code so I'll just dump it off and let it be somebody else's problem" feature. I have never seen T/C well implemented and I suspect that it, by nature, cannot be.
Let me translate the reasoning behind the addition of this feature to the SQL product line for you: We're (MSoft) putting way too many interdependent and entangled bells an whistles (while breaking *more* fundamental principles) in this next release of the database, so instead of making sure it all works right, add this new thing "try/catch" so that when the sloppy, ilconceived code we (MS) wrote breaks we can "throw" an error and make the application developer have to deal with it... We'll sell it as a "feature", yeah, that's the ticket...
We're doomed I tell you...
-
Actually I would consider this structured error handling an improvement.
The crux with it, as you have noticed, is that it is mostly poorly used. Btw, many people really shouldn't use C++ anyway, but that's another topic
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I have to agree with Frank. I'm happy to see try/catch in TSQL. Right now I have to code all sorts of crap to handle exceptions that aren't MS' fault, but the fault of prior developers who left me holding the bag on some pretty lousy apps. Plus I have users who don't understand the concept of following the directions. Despite my best efforts on the front-end things slip through to the back end and I have to make sure I can catch it.
Also, what if you (not you John, but the generic "you") write some TSQL that is just plain wrong. At least if it's in a try/catch block and you do a reasonable job of error handling you might get some useful information that will help you debug the problem. It's better than PRINT statements and Query Analyzer...
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply