Subroutines in TSQL

Question

Post reply

Subroutines in TSQL

sihaab

SSCommitted

Points: 1742
More actions
January 14, 2010 at 12:48 pm

#209130

I am a heavy TSQL coder, I love the many aspects of TSQL but I always wonder why we should not have a sub-routines in TSQL as other languages, why should I create a function for it. Many times, I write ad-hoc script and not necessarily useful for others but me, because I heavily involved in data analysis process. Sub-routines are very useful for anyone who loves programming.
How can we convince Microsoft to Include this facility in TSQL?
Ideas appreciated.

Viewing 15 posts - 1 through 15 (of 96 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Curious how you would use a subroutine in T-SQL. Can you show an example of how this would work if it were possible?

GSquared SSC Guru Points: 260824 More actions · Answer 2

I guess I'm not clear on what you mean by subroutines here. One stored procedure can call others, which is how I'm used to thinking of subroutines - code called by other code. Is that not what you mean?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sihaab SSCommitted Points: 1742 More actions · Answer 3

Try this and more on Google as well...

http://www.codeproject.com/KB/aspnet/VBnet_Methods.aspx

GSquared SSC Guru Points: 260824 More actions · Answer 4

sihaab (1/14/2010)

Try this and more on Google as well...

http://www.codeproject.com/KB/aspnet/VBnet_Methods.aspx%5B/quote%5D

That's talking about exactly what I already mentioned: Stored procedures called by other stored procedures. (That's the SQL version of it anyway.) The article needs editing (the English in it is pretty mangled), but it's a pretty basic explanation of the process. In SQL, it's done by creating a stored procedure, and then having other scripts or procedures call it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sihaab SSCommitted Points: 1742 More actions · Answer 5

Sub routines are not stored procedures or functions. Subroutines can reside in the same stored procedure or function or in ad-hoc TSQL scripts. Normally, this is achieved by TSQL GOTO and LABEL commands, but subroutines give more flexibilities to the scripts. I hope my explanation helps...

Ray K SSC-Dedicated Points: 31148 More actions · Answer 6

I think I understand where you're going with this.

How would a subroutine work in a set-based, rather than a RBAR, environment?

If you have any examples to provide, please enlighten us!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Ray K SSC-Dedicated Points: 31148 More actions · Answer 7

Another question (again, I think I understand, but I'm looking to clarify): how would a subroutine differ from a CTE? What would a subroutine do that a CTE can't do?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

sihaab SSCommitted Points: 1742 More actions · Answer 8

Think of a subroutine as a function but this function resides at the same procedure, not outside of the procedure. For example, if you call a function from a stored procedure or from an ad-hot script, you have to have the function created and saved at the database. If that function is not useful after this ad-hot script, then you have to delete it from the database as well. In that case, I can convert this function as a subroutine in my script and I don't have to create any function at all. There are plenty of examples on the web for subroutines. We can still use TSQL efficiently without subroutines for sure... I am just thinking what if we have it...

Here is link for VBScript subroutines..

http://www.go4expert.com/forums/showthread.php?t=4098

Ray K SSC-Dedicated Points: 31148 More actions · Answer 9

I come from a VBScript/VB.NET/OOP background myself, so I understand the subroutine concept! 🙂

I'm just trying to see how it's applicable to SQL. Since I've lived on both sides of the SQL/OOP fence, I'm wondering what kind of an advantage a SQL subroutine would have over anything that already exists.

Don't get me wrong; I'm not trying to shoot down your idea (in fact, I think it's intriguing). I'm just trying to figure out how it would apply to SQL (and maybe play a little bit of devil's advocate)! 😉

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

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

I'm at a bit of a loss...

I have years of VB experience and now I code all day long in SQL Stored Procedures. I have never found a reason for having a sub process...

If I'm doing something that is complex more than once, I try to do it once and save off the results so that I can refer back to those instead of running the complex code again.

Kev -=Conan The Canadian=-
@ConanTheCdn

Ray K SSC-Dedicated Points: 31148 More actions · Answer 11

Conan The Coder (1/14/2010)
If I'm doing something that is complex more than once, I try to do it once and save off the results so that I can refer back to those instead of running the complex code again.

This is a very good point. If there's one thing I've learned about lurking around these forums, it's that a good chunk of SQL is about efficient processing. I'm wondering how much processing time would be added by using a subroutine call to regenerate data, rather than using something that already exists.

Like I said before, I do think the idea is interesting. It's just that, unless it provides a very clear advantage over existing tools, I don't see it happening.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

I'm not a GUI programmer and it's been years since I've touched even VB6 never mind VB.Net. But it sounds like a cursor (GAH!) could serve as a "subroutine".

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 13

You can do this with SQL Server 2008 and the new table value parameters.

If you are working on a set of data, you can pass that set of data to another procedure for further processing.

N 56°04'39.16"
E 12°55'05.25"

Ray K SSC-Dedicated Points: 31148 More actions · Answer 14

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! 😀

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/