March 14, 2009 at 5:01 pm
The question is a problem. Update the product table given above with the correct descriptionID with out cursors.
March 14, 2009 at 5:05 pm
Okay, foxjazz, you can rant incoherently that is obvious. How good are you at following directions? As Jeff requested, read THIS[/url]. Follow the steps in that article precisely, and provide us with table definitions, sample data, what you want us to accomplish, and the expected results.
Once you do that, we will do our thing and show you it can be done without using cursors! If, however, you can't follow directions, perhaps you should just pick up your toys and go home.
March 14, 2009 at 5:30 pm
You know foxjazz, I find the following comment of yours insulting and ignorant on your part:
You all that use t-sql and don't code probably never know any difference.
You are basically saying that all of us who write using T-SQL don't write code. You don't know a thing about us and what we do at our work. I am not just a DBA at work, I am a developer and architect as well.
I also come from a strong development background even if i didn't write code in C#. Don't presume to know what we do or don't know.
March 14, 2009 at 6:39 pm
Lynn Pettis (3/14/2009)
You know foxjazz, I find the following comment of yours insulting and ignorant on your part:You all that use t-sql and don't code probably never know any difference.
You are basically saying that all of us who write using T-SQL don't write code. You don't know a thing about us and what we do at our work. I am not just a DBA at work, I am a developer and architect as well.
I also come from a strong development background even if i didn't write code in C#. Don't presume to know what we do or don't know.
Um, saying all that use t-sql and don't code is simply that. Lynn, since you are including yourself in this statement, I don't think you have performed adequate logic on the sentence at hand. Treat it like a query, maybe you can read it better. It is a true statement, all who don't code and only use t-sql wouldn't know any better.
I don't think that includes you Lynn, since obviously you say you code.
March 14, 2009 at 6:47 pm
Instead of having you guys try and figure out some problem without the use of cursors, I have already made my point without realizing that I had.
Simply because cursors are part and parsel of t-sql is that a good thing?
Would t-sql be made better without cursors?
Can you do all things necessary to a sql database without the use of cursors?
If the question is yes, then they should have dated them. No new t-sql querys may be made with cursors should be the rule.
If it is false, and cursors exist to perform a significant function within the t-sql camp, even though they are "bad" and shouldn't be used, it is inately a flaw in the software.
I haven't experienced a case where I could write a stored proc, without t-sql language. If anyone knows how to do this, please let me know. I would love to write my stored procs in c#. But since no one has spoken up on this, I am going to assume for the moment it isn't possible.
Why no one but me, is complaining about this is beyond my understanding.
March 14, 2009 at 7:06 pm
foxjazz (3/14/2009)
Ok, yes I did it that way before. Except you have to do it in 4 distinct query sets, and link the first results to the other set, and so on.Ok, another problem:
Lets say you have a "product table with a descriptionID that links to a description table.
In another database, there is a product and description table.
This product table has lots of field in it,ProductID, productlineID, partnumber, searchnumber, upc etc... most of which we don't care about.
Partnumber and productlineid are unique. productid is the uniqueKey.
The only productlineid we are worried about in this exercise is where it is 1 all the others can just be there for other reasons.
in the first table we say select partnumber, description and get a set of rows.
38101, belt
7404, 'drive align tensioner'
These descriptoins may change over time and lets say they do change but te changes are small (like instead of 'drive align tensionser' its now just 'tensioner'.
so the first goal is to find all the changed items from the first table, which is simple using a outer join, where product is null.
So we have a few partnumbers that are listed (which are already in the product table, so no inserts are necessary).
select partnumber, description from FIRSTTABLE would be the first query
now for each partnumber you receive, you have to update the descriptionID using the productID so the structure looks like this:
table = productdescription
descriptionID int,
description nvarchar(50)
table = product
productid int, primarykey
partnumber nvarchar(50),
productlineid int,
descriptionid int,
other fields of other uses...
so this would be a valid query.
select @productid = productid, description from product inner join productdescription on product.descriptionid = productdescription.descriptioinid
where partnumber = '38101'
and this would be legal.
Update product set descriptionid = @descriptionid where productid = @productid
however you have to check the descriptiontable to see if there is a valid description, if not you have to insert one.
select @cnt = count(*), @descriptionid = descriptionid from productdescription where description = 'tensioner'
if @cnt = 0
begin
insert productdescription(description) values ('tensioner')
update product set descriptionid = @@identity where productid = @productid
end
else
begin
update product set descriptionid = @descriptionid where productid = @productid
end
This is a real world problem that can't be done with set statements, you have to run a cursor because you can't make more than one primarykey update to the table to make it have the correct description with one update.
A simple "upsert" or "merge" would do it neatly without a cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2009 at 7:23 pm
foxjazz (3/14/2009)
...Ok, another problem:Lets say you have a "product table with a descriptionID that links to a description table.
In another database, there is a product and description table.
This product table has lots of field in it,ProductID, productlineID, partnumber, searchnumber, upc etc... most of which we don't care about.
Partnumber and productlineid are unique. productid is the uniqueKey.
The only productlineid we are worried about in this exercise is where it is 1 all the others can just be there for other reasons.
in the first table we say select partnumber, description and get a set of rows.
38101, belt
7404, 'drive align tensioner'
These descriptoins may change over time and lets say they do change but te changes are small (like instead of 'drive align tensionser' its now just 'tensioner'.
so the first goal is to find all the changed items from the first table, which is simple using a outer join, where product is null.
So we have a few partnumbers that are listed (which are already in the product table, so no inserts are necessary).
select partnumber, description from FIRSTTABLE would be the first query
now for each partnumber you receive, you have to update the descriptionID using the productID so the structure looks like this:
table = productdescription
descriptionID int,
description nvarchar(50)
table = product
productid int, primarykey
partnumber nvarchar(50),
productlineid int,
descriptionid int,
other fields of other uses...
so this would be a valid query.
select @productid = productid, description from product inner join productdescription on product.descriptionid = productdescription.descriptioinid
where partnumber = '38101'
and this would be legal.
Update product set descriptionid = @descriptionid where productid = @productid
however you have to check the descriptiontable to see if there is a valid description, if not you have to insert one.
select @cnt = count(*), @descriptionid = descriptionid from productdescription where description = 'tensioner'
if @cnt = 0
begin
insert productdescription(description) values ('tensioner')
update product set descriptionid = @@identity where productid = @productid
end
else
begin
update product set descriptionid = @descriptionid where productid = @productid
end
This is a real world problem that can't be done with set statements, you have to run a cursor because you can't make more than one primarykey update to the table to make it have the correct description with one update.
I have spent the last hour trying to decipher this mess. As I understand it you have a table like this:
CREATE TABLE FIRSTTABLE(
partnumber Nvarchar(50),
description Nvarchar(50)
);
That you want to use to update the descriptionid's in your product table:
CREATE TABLE product(
productid int primary key,
partnumber nvarchar(50),
productlineid int,
descriptionid int,
WhatEver varchar(max)
);
However, your productdescription table may not have all of the descriptions yet:
CREATE TABLE productdescription(
descriptionID int Primary Key,
description nvarchar(50)
);
And you believe that this can only be done with a cursor. Not even close:
--======= Make sure that productdescription has all of the descriptions:
INSERT Into productdescription(description)
Select description
From FIRSTTABLE
Where NOT description IN (Select pd2.description
From productdescription pd2);
--====== Correct all of the descriptionid's based on FIRSTTABLE:
UPDATE product
Set descriptionid = pd.descriptionid
From productdescription pd
Join FIRSTTABLE ft ON ft.description = pd.description
Where ft.partnumber = product.partnumber
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 14, 2009 at 7:23 pm
foxjazz (3/14/2009)
Instead of having you guys try and figure out some problem without the use of cursors, I have already made my point without realizing that I had.Simply because cursors are part and parsel of t-sql is that a good thing?
Would t-sql be made better without cursors?
Can you do all things necessary to a sql database without the use of cursors?
If the question is yes, then they should have dated them. No new t-sql querys may be made with cursors should be the rule.
If it is false, and cursors exist to perform a significant function within the t-sql camp, even though they are "bad" and shouldn't be used, it is inately a flaw in the software.
I haven't experienced a case where I could write a stored proc, without t-sql language. If anyone knows how to do this, please let me know. I would love to write my stored procs in c#. But since no one has spoken up on this, I am going to assume for the moment it isn't possible.
Why no one but me, is complaining about this is beyond my understanding.
Heh... I actually agree... Cursors are there for people with real life problems that don't know how to solve them using set based technology because they don't know enough about database programming. But, I don't complain because they're so much $fun$ to fix...
And, if folks want to, they could actually write all their stored procedures in C# and deploy them in the form of CLR's... that way, they don't actually have to learn anything about database programming to git 'er done. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2009 at 7:34 pm
OK, foxjazz, now I have a question for you:
foxjazz (3/14/2009)
Ok, yes I did it that way before. Except you have to do it in 4 distinct query sets, and link the first results to the other set, and so on.Ok, another problem:...
We have now easily solved three of your "impossible" cursor problems without any cursors. The only difficult thing about any of these has been trying to figure out what you are talking about. How many more of these do we have to solve before you accept that you problem with SQL is not shortcomings in SQL, but rather shortcomings in your understanding of SQL?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 14, 2009 at 7:39 pm
RBarryYoung (3/14/2009)
OK, foxjazz, now I have a question for you:foxjazz (3/14/2009)
Ok, yes I did it that way before. Except you have to do it in 4 distinct query sets, and link the first results to the other set, and so on.Ok, another problem:...
We have now easily solved three of your "impossible" cursor problems without any cursors. The only difficult thing about any of these has been trying to figure out what you are talking about. How many more of these do we have to solve before you accept that you problem with SQL is not shortcomings in SQL, but rather shortcomings in your understanding of SQL?
I have this vision of Tom Sawyer white-washing a fence. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2009 at 7:41 pm
foxjazz (3/14/2009)
Why no one but me, is complaining about this is beyond my understanding.
Well, the reason might be very simple:
most of the others (C# programmers I presume you're talking about) may either know how to use T-SQL in depth, getting themselves educated on how to do it if they don't know yet, or let the T-SQL stuff with the folks that know how to use it.
foxjazz (3/14/2009)
... No new t-sql querys may be made with cursors should be the rule.
From my perspective, cursors are available in T-SQL as a last fall back line:
If someone doesn't see any other way on how to deal with the requirements he's faced with, he can go back and use a cursor. But he need to be aware of the fact that he might struggle with performance.
Some SQL folks will come to that point sooner, some later, and some of them rarely if at all. Usually, the latter ones spent more time on trying to avoid cursors and do have a deeper knowledge about T-SQL than the other.
So, if you don't look for a way to avoid cursors, you won't find one.
Note off topic: If I feel my car's too slow I can complain about it or just figure out how shift gears... :hehe:
March 14, 2009 at 7:42 pm
foxjazz (3/14/2009)
Instead of having you guys try and figure out some problem without the use of cursors, I have already made my point without realizing that I had.
No, you were right the first time. You have not made your point. Not even remotely close to it.
Simply because cursors are part and parsel of t-sql is that a good thing?
Would t-sql be made better without cursors?
Yes. In the same way that Southeast Asia would be better off without landmines.
Can you do all things necessary to a sql database without the use of cursors?
Yes. We have been proving that to you.
If the question is yes, then they should have dated them. No new t-sql querys may be made with cursors should be the rule.
True. But irrelevant, because that is not what we have been talking about. We have been talking about whether or not you were correct when you claimed that you needed cursors to do you job or we were correct that you just did not know how to use SQL well enough to do it right.
Stop trying to change the subject everytime you realize that you are wrong.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 14, 2009 at 7:48 pm
Do any of you know a language other than English? Spanish, as a second or German?
Because using English is dominant, I struggle with German as a second. I can use it, but why should I be made to use it?
I personally am highly familiar with t-sql, but do most of my stuff in c#. Why am I forced to write in this other language?
Knowing how to do stuff, and prefering to do it differently are two vastly different things. I think that is where most of the confusion comes from.
I like c# way over vb, and when I am coding in VB and I put a ; at the end of the line, my IDE is smart enough to delete it for me.
This is the kind of stuff I am talking about. It's habitual. Why not have t-sql be capable of merging with other languages by preference?
March 14, 2009 at 8:01 pm
Heh... definitely not. English only here. Of course, if I need something done in Germany, I get a translator.
Same goes with code... GUI programmers shouldn't have to write database code... they should let SQL Developers translate that for them.
By the same token, there's a huge difference between cars and trucks. You wouldn't use your little ol' red sports car to haul a ton of Earth...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2009 at 8:05 pm
foxjazz (3/14/2009)
Why not have t-sql be capable of merging with other languages by preference?
Heh... I told you that it actually is, before... you can write all your stored procedures in C# and convert them to CLR's for SQL. Go for it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 465 total)
You must be logged in to reply to this topic. Login to reply