T-SQL 2005 Help

Question

T-SQL 2005 Help

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Bob Hovious (4/11/2009)
You should feel safer now, too. 😉

:rolleyes:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Bob Hovious (4/11/2009)
You should feel safer now, too. 😉

Well said.

Now if I could get Kalen to agree too I'd feel extremely safe. 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

I remember a case my apprentice told me from one of his teachers, the "IF-loop". Sure a slip of the tongue, but anyway:

DECLARE @i INT

SET @i = 1

START:

PRINT 'Hello'

SET @i = @i + 1

IF (@i < 5) GOTO START

:w00t: 😀

Greets

Flo

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Florian Reischl (4/11/2009)
I remember a case my apprentice told me from one of his teachers, the "IF-loop". Sure a slip of the tongue, but anyway:
DECLARE @i INT
SET @i = 1
START:
PRINT 'Hello'
SET @i = @i + 1
IF (@i < 5) GOTO START

Ok, Why the GOTO there? Missing the Do.. While loop so much?

DECLARE @i INT

SET @i = 1

WHILE (@i < 5)

BEGIN

PRINT 'Hello'

SET @i = @i + 1

END

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

GilaMonster (4/11/2009)
Shane Redding (4/11/2009)
In regards to the below, perhaps you should go work for the microsoft sql server team as GOTO is not uncommon in TSQL. 🙂
Just because the MS people do it doesn't make it a universally good idea. Witness the design of MSDB or some of the code that management studio generates for a number of examples of that.

Hear here! I absolutely agree. For proof of that, take a peek at sp_SpaceUsed and how the sysProcesses table (in 2k) was built.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

GilaMonster (4/11/2009)
Florian Reischl (4/11/2009)
I remember a case my apprentice told me from one of his teachers, the "IF-loop". Sure a slip of the tongue, but anyway:
DECLARE @i INT
SET @i = 1
START:
PRINT 'Hello'
SET @i = @i + 1
IF (@i < 5) GOTO START
Ok, Why the GOTO there? Missing the Do.. While loop so much?
DECLARE @i INT
SET @i = 1
WHILE (@i < 5)
BEGIN
PRINT 'Hello'
SET @i = @i + 1
END

Hi Gail

The GOTO-loop was just a little joke :hehe:

Greets

Flo

ABAS101 SSCommitted Points: 1894 More actions · Answer 7

Out of curiosity, have you ever created a job in SQL Server with multiple steps and tell it what to do upon failure? If so, then you've used GOTO. GOTO's have their place inside code if used properly. It's called "Controlled Flow" for a reason. Now I'll admit for what this person was trying to do, it might or might not be incorrect (I'll gladly admit that me "Failing" earlier.)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

ABAS101 (4/11/2009)
Out of curiosity, have you ever created a job in SQL Server with multiple steps and tell it what to do upon failure? If so, then you've used GOTO.

Incorrect. TRY..CATCH is not a GOTO anymore then IF..THEN is.

GOTO's have their place inside code if used properly.

Also incorrect. GOTO's only place outside of Macro Assemblers and some (few) Device Drivers is in those few remaining languages so primitive and backwards that they lack the proper control structures to do programming correctly. For a long time several popular languages had some shortcomings with respect to error-handling, but this hole has been fixed for at least 4 years now.

It's called "Controlled Flow" for a reason.

Actually it's called "Control Flow", and not because it has anything to do with GOTO.

GOTO was an aberrant mistake in the progress of software engineering a fact that was brilliantly argued in 1968 in the most famous computer programming article ever written. The correctness of this argument was hotly debated by virtually everyone in the field for ten years througout the 70's. By 1980, this was considered a settled matter by everyone of any standing and all that remained was to sadly slow process of waiting for the capabilities of the individual languages to catch up to what was then (and now) known to be the minimum requirements for a modern programming language.

[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]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

All of that may be true... I just hope they never remove the capability because it is an option. It's kind of like cursors... I generally deplore their usage but every once in a blue moon, nothing else will do.

And, no... I have no example of where a GOTO might actually be better. But, if they were to remove it from the language, that option would no longer exist. That brings me to another subject... I hate deprecation. Who is it to say that certain features are no longer useful? The masses? BWAA-HAAA-HAAA!!! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ABAS101 SSCommitted Points: 1894 More actions · Answer 10

In regards to the article about "Go To Statement Considered Harmful," WOW. If technology hasn't gotten any better since 1980's, I'll go back to my old job. Wait, that job is long gone. Now I agree that back then, it was not such a good thing but modern day compilers have definitely advanced since then and hopefully everyone here has continued improving their skills over the years and not still living in the 70's/80's though I have worked with a few people that fall into that category.

In regards to my reference to "Controlled Flow" and you correcting me to "Control Flow". I'm speechless as there is a major difference there.

AND Jeff, LOL, I don't think any of us have it in us to argue about Cursors right now but I knew it was just a matter of time before it got thrown in here. 😀

James.N SSC Enthusiast Points: 193 More actions · Answer 11

Thanks to you all for all your inputs.

my req is :

If A=null

execute stored prod 1

else

check If A=c

execute stored prod 1

meaning, if the column value is null (this is null only for first run.)then insert a value to it and then insert the records to all othe tables. if the column is not null(this satisfies from second run) check for a condition . if conditions satisfies do the same insertion of tables else stop the process.

I made this work by copying the same insertion code two times, but i want to get rid of typing my same code two times

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

rajamohangade (4/12/2009)
Thanks to you all for all your inputs.
my req is :
If A=null
execute stored prod 1
else
check If A=c
execute stored prod 1
meaning, if the column value is null (this is null only for first run.)then insert a value to it and then insert the records to all othe tables. if the column is not null(this satisfies from second run) check for a condition . if conditions satisfies do the same insertion of tables else stop the process.
I made this work by copying the same insertion code two times, but i want to get rid of typing my same code two times

There have already been several replies to your original question. If they were not sufficient then you should reply with some indication that you have read them and are trying to comply with our requests. Just repeating your question doesn't help.

Here is the most important request, (which was also the first reply to your questions):

Bob Hovious (4/10/2009)
If you would please write a short script to define temporary tables and put a few rows into them, then people can test their solutions to your problem before posting coded replies. You might take a moment to look at the article here[/url]. Following the examples for posting will not only get you tested answers faster, it will encourage more people to tackle your problem, and win you friends among the volunteers who are helping you.
Thanks 🙂

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

ABAS101 (4/12/2009)
In regards to the article about "Go To Statement Considered Harmful," WOW. If technology hasn't gotten any better since 1980's, I'll go back to my old job. Wait, that job is long gone. Now I agree that back then, it was not such a good thing but modern day compilers have definitely advanced since then and hopefully everyone here has continued improving their skills over the years and not still living in the 70's/80's though I have worked with a few people that fall into that category.

Perhaps you should actually read the article before you attempt to pontificate about it, because your statements here make it clear that you have no idea what it says or what it's central argument is.

In regards to my reference to "Controlled Flow" and you correcting me to "Control Flow". I'm speechless as there is a major difference there.

Precision is important in our profession. And when you are attempting to make an argument about the meaning of a phrase of jargon, it's pretty important to get the phrase correct. Getting it wrong not only changes it from a specific jargonistic meaning to a mere unrelated phrase of English, it also indicates both that you are not very familiar with it or with our profession and adds an element of pretension to your entire discussion here.

[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]

ABAS101 SSCommitted Points: 1894 More actions · Answer 14

Oh I've read it. Years ago as well as quite a bit of arguments made after that as recently as this decade. The original piece if I am correct was written back in the 60's, but the author didn't come out and say to completely eliminate the GOTO did he? If you want to change my mind on it being bad programming today and how it has a negative impact on things, please show me recent proof. Just as with any code, someone could always turn it into FUBAR so that argument doesn't stand. You perhaps might persuade me with visual proof though 😉 Until then, I'll move on to other issues as this is getting rather old going back and forth. People, as well as myself, have better things to move on to.

And the "Control Flow" precision comments...Getting that "Wrong" as you put it, did NOT change the context of it and does not make myself nor others on these forums who have those kinds of typos Non Professionals in our professions.

Truthful, most of what you comment on aside from this particular subject, I agree with. Hope you sleep better knowing I do not 100% always disagree with you. Just 99% (JK). We agree to disagree 🙂

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 15

Sorry... had a change of thought... posted those thoughts further below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)