Subroutines in TSQL

Question

Subroutines in TSQL

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Ray K (1/15/2010)
Jeff Moden (1/14/2010)
But it sounds like a cursor (GAH!) could serve as a "subroutine".
I'll bet it was really painful for you to type this! 😀

Heh... yeah... hand still has a cramp in it. 😛

Shifting gears, I wish people would understand... T-SQL ISN'T a GUI language and I hope they never change the basic way that it operates. Adding subroutines would be such a change and invite even more folks that don't really know how to use databases to use them. :hehe:

--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)
Intro to Tally Tables and Functions

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

I think what the OP is getting at is a replacement for this structure:

--= some very high performance sql set based code

--....

IF @result = 1

BEGIN

SET @calledby = 1

GOTO mylabel

:mylabelreturn1

END

--...

--= some other most excellent (probably Tally based) select

--...

IF @result2 = 1

BEGIN

SET @calledby = 2

GOTO mylabel

:mylabelreturn2

END

--...

RETURN

:mylabel

--= some highly optimised code

--...

IF @calledby = 1

GOTO mylabelreturn1

IF @calledby = 2

GOTO mylabelreturn2

--etc.

Replaced by this

--= some very high performance sql set based code

--....

IF @result = 1 GOSUB mysubroutine

--...

--= some other most excellent (probably Tally based) select

--...

IF @result2 = 1 GOSUB mysubroutine

--...

RETURN

:mysubroutine

--= some highly optimised code

--...

RETURNFROMSUB

(excuse the pseudo code but it conveys the point)

And I for one (Although it is rare I would code like that) would not vote against a new syntax that allowed for that "sort of thing".

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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

Understood and we already have that (it's what I meant when I said it sounds like a cursor)... it just doesn't need to be inline...

--= some very high performance sql set based code

--....

IF @result = 1 EXEC somestoredprocedure

--...

--= some other most excellent (probably Tally based) select

--...

IF @result2 = 1 EXEC somestoredprocedure

--...

RETURN

Subroutine = Stored procedure. It's just a different way of thinking.

--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)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (1/16/2010)
Understood and we already have that (it's what I meant when I said it sounds like a cursor)... it just doesn't need to be inline...
--= some very high performance sql set based code
--....
--....
IF @result = 1 EXEC somestoredprocedure
--...
--...
--= some other most excellent (probably Tally based) select
--...
IF @result2 = 1 EXEC somestoredprocedure
--...
RETURN
Subroutine = Stored procedure. It's just a different way of thinking.

While it technically still exists in .NET languages, GOTO will get you betch-smacked by the application architect in my shop, and pretty much any other shop I currently know of unless you happen to be writing some DOS-level vbscript, etc...

All higher-level OO languages these days have much more elegant way to handle branching than labels and GOTO/GOSUB. I'd be curious to know why that would be needed OUTSIDE of SQL as well.

That stuff was "the Devil" (to quote Mama Boucher from the Waterboy): very hard to follow, usually hard to maintain, and often unstable as heck, unless used in the most basic of scripts devoid of any other way to do branching.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Ummmm.... where do you see a GOTO in any of that? 😉

--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)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (1/17/2010)
Ummmm.... where do you see a GOTO in any of that? 😉

I didn't.

I'm saying that most OO languages these days would tend to do branching in the very same way SQL does it (i.e. your code). GOTO's and label-branching tend to be unstable. I was trying to question why the OP would be so interested in this construct under any circumstance (SQL or no).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Ah... got it. Thanks, Matt.

--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)
Intro to Tally Tables and Functions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Matt Miller (#4) (1/17/2010)
Jeff Moden (1/17/2010)
Ummmm.... where do you see a GOTO in any of that? 😉
I didn't.
I'm saying that most OO languages these days would tend to do branching in the very same way SQL does it (i.e. your code). GOTO's and label-branching tend to be unstable. I was trying to question why the OP would be so interested in this construct under any circumstance (SQL or no).

Can you please explain your 'unstable' comment please? Unstable to me implies correctly coded stuff (i.e. completely functional) that will break due to a problem with the interpreter, compiler, execution environment, etc. I wasn't aware of problems of that nature with GOTO/label-branching.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Revenant SSC-Forever Points: 42467 More actions · Answer 9

It goes back to Dijkstra's 1968 paper:

http://www.cs.utexas.edu/users/EWD/transcriptions/EWD02xx/EWD215.html

GOTO-based subs are dangerous because you have to maintain your own variable that directs you back where you came from. It does not help much if a high-level programming language such as PL/I has label type variables.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

TheSQLGuru (1/18/2010)
Matt Miller (#4) (1/17/2010)
Jeff Moden (1/17/2010)
Ummmm.... where do you see a GOTO in any of that? 😉
I didn't.
I'm saying that most OO languages these days would tend to do branching in the very same way SQL does it (i.e. your code). GOTO's and label-branching tend to be unstable. I was trying to question why the OP would be so interested in this construct under any circumstance (SQL or no).
Can you please explain your 'unstable' comment please? Unstable to me implies correctly coded stuff (i.e. completely functional) that will break due to a problem with the interpreter, compiler, execution environment, etc. I wasn't aware of problems of that nature with GOTO/label-branching.

no - unstable as in - very easy to break especially during ongoing dev, hard to debug, tough to trace, etc....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

I fall squarely in the use the right tool for the job camp. And if I decide that a GOTO is the right tool for the job, that is what will get used. I certainly won't let a paper from 1968 sway that decision. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Conan The Canadian Ten Centuries Points: 1335 More actions · Answer 12

TheSQLGuru (1/18/2010)
I fall squarely in the use the right tool for the job camp. And if I decide that a GOTO is the right tool for the job, that is what will get used. I certainly won't let a paper from 1968 sway that decision. 🙂

I believe in using GOTO statements in the right contex as well.

However, they can be abused by jumping all over the place in a stored procedure. When this happens any modifications will take much longer because the code is less readable and the modifications are high risk for introducing bugs.

Just my 2 cents.

Kev -=Conan The Canadian=-
@ConanTheCdn

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

TheSQLGuru (1/18/2010)
I fall squarely in the use the right tool for the job camp. And if I decide that a GOTO is the right tool for the job, that is what will get used. I certainly won't let a paper from 1968 sway that decision. 🙂

No doubt. It's right up there with cursors and RBAR in this realm for me...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Revenant SSC-Forever Points: 42467 More actions · Answer 14

When I quoted Dijkstra, I did not want to say that GOTO must not be used and yes, it can be and often is the right tool for thed task at hand. However, it IS dangerous, so if you use it, one should be aware of its dangers and limitations. That's all.

konstanddp Valued Member Points: 69 More actions · Answer 15

Hi all

hope this discussion is still active.

I am sitting with exactly the scenario where I would have liked to be able to call a subroutine within a sql script.

I am building a script that imports a csv file from a client that we use to update our "master" data. Now after many changes to the format of the file, we were instructed that we will get a file from every manager containing their data, meaning the process must be repeated a few times.

At the beginning how ever an update statement is executed that to make all data inactive so that only data in the new files are left active after the import.

In the .Net world the ideal would be to do this:

sub main()

SubUpdate()

SubImport("c:\file1.csv",....)

SubImport("c:\file2.csv",....)

SubImport("c:\file3.csv",....)

end sub

and then followed by the two subs...

The fact is that the two subroutines will never live alone as an independant stored procedure and will always be called in conjunction. can anyone tell me how I do this in SQL?

thanks

CK