Long SQL Script coding style question - use of GOTO

Question

Post reply

Long SQL Script coding style question - use of GOTO

Tom John-342103

SSCrazy

Points: 2694
More actions
August 3, 2006 at 8:14 am

#118900

SQL script syntax doesn't seem to lend itself to any sort of structure. If you have a long, complex script, what are some options to impose some structure.
One option I am considering is to use GOTO statements so that I can have a summary main processing flow with the complex detailed sections farther down in the script. So, I can GOTO a detail section and the detail section will have a GOTO that returns processing to a point in the main section.
What are some other options.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why not call functions like you would do in a vb or vb net application?

Pretty much all solutions are better than goto (except for err handling).

Sergiy SSC Guru Points: 110208 More actions · Answer 2

You can call SP from another SP.

If you believe that "SQL script syntax doesn't seem to lend itself to any sort of structure" then you don't know SQL.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

"doesn't seem to lend itself to any sort of structure"???

As Serqiy said, you can create stored procedures that act as "main" routines that call other sometimes reuseable stored procedures as if they were "sub-routines" (old word, I'm showing my age). As Reggie suggested, you can make user defined functions and the like. You can create views to solve interim processing problems. The hard part is, it's totally up to you... you don't have some slick GUI saying "oops, that's not quite right there". Then there's CASE statements, IF BEGIN/END for flow control, and a wad of other things. It's ALL up to you and you're going to have to study a bit to see just how flexible and powerful SQL really is.

If you're expecting Object Oriented Programming as in Java, you may be a bit disappointed because there's nothing to force you to follow any structure at all. It's all up to you. You can be lax or strict or anything in between.

As Reggie pointed out, except for error handling, you should avoid GOTO at all costs. As Serqiy pointed out, if you think otherwise, then you may have a bit more studying to do.

--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)

Tom John-342103 SSCrazy Points: 2694 More actions · Answer 4

I am familiar with all that has been said in this thread. I was hoping that somebody would tell me there was something akin to PERFORM paragraph-name in COBOL lurking somewhere in the shadows.

I know that I can call SPs from SPs and I can see where functions can be useful. But, absent reusability, I don't see much point in creating other components just to keep a single script of a manageable size.

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

I agree... breaking up a proc/script just to control size is a bit stupid but using GOTO, as you suggested, isn't the way to do it. CASE and IF BEGIN/END is...

--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)

Tom John-342103 SSCrazy Points: 2694 More actions · Answer 6

As a former COBOL programmer, the problems of GOTO have been understood from the beginning.

But, what I found is that a careful use of GOTO is effective. In the main processing section of the script, I use GOTO to go to subsections of detail logic. At the end of subsection, I use a second GOTO to return control to a label immediately following the first GOTO. In this way, I don't have an indiscriminate use of GOTO such that the processing flow is impossible to follow. However, I am able to structure the script in such a way that there is a main flow of control uncluttered by the detail of the subsections.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Tom, did they really teach you in school that structural programming is only about reusability?

Ask any developer - do they create separate method only if it's ment to be reused in another routine?

SP, as well as a method, must be short, readable and maintainable.

It's funny to say it here. It's kind of "first week" knowledge on any programming courses.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Tom,

I agree with Serqiy... write other sp's to do one part of your process well... write as many as you need... then, go ahead and call them from a "main"... writing GOTO's into SQL is a form of "Death by SQL". It WILL eventually catch up to you...

And, most of the guys I used to know that programmed in COBOL would call other modules instead of using GOTO's as you propose.

--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)

Tom John-342103 SSCrazy Points: 2694 More actions · Answer 9

Jeff,

I appreciate your mellow answers. Anyway, I am starting to lean towards SPs as subprograms. It saves me having to fish through a long script no matter how well structured.