SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subroutines in TSQL


Subroutines in TSQL

Author
Message
Paul White
Paul White
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45585 Visits: 11368
Eric M Russell (3/3/2011)
My most recent involvement with querying remote data involves joining huge resultsets originating in a 3rd party Oracle 11g database with reference data in a SQL Server 2008 datamart, and then inserting the result into into the same SQL Server datamart for later use by the reporting team. The SQL Server DTC and Oracle don't share much, and early experiments with 4 part named queries would result in entire table scans being pulled across the wire. When I EXEC a pass-though query to Oracle, I'm only getting a small DTC overhead, and the remaining 1 - 30 minutes of processing time occurs entirely on Oracle, and then however much time it takes to bring the final resultset back across the wire. Joining the temporary resultset with the reference tables is trivial at that point.

Yes, I've been in a similar boat and feel your pain. Distributed query is not perfect by any means, though some of the problems can be traced to the remote end which may not provide the optimizer will good (or any) statistical information about the remote data. No doubt this is especially problematic where the remote server is not a SQL Server (it's been a few years since I had to work with Oracle). Do look into Gus' suggestion re: table variables to avoid a distributed transaction, or if 2008, try the remote transaction promotion option I described: it will prevent the local INSERT implicit transaction from being promoted to a full DTC transaction.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)

Group: General Forum Members
Points: 278084 Visits: 42244
Telarian (3/3/2011)
Jeff Moden (3/3/2011)


Oh, be careful now. "It Depends". :-) Scalar and Multi-Line Table Valued Functions (mTVF) can very quickly turn code into some pretty nasty, performance challenged RBAR especially if it refers to a table. Think of it as a hidden cursor.

Inline Table Valued Functions (iTVF), however, can be VERY useful and are calculated in the execution plan as if they were a "programmable view". Oddly enough, they also work very effectively to return single values like a Scalar function would when used with CROSS APPLY (thanks to Paul White for that nice little trick).


Don't get me wrong, I'm not saying there aren't usually better ways to do things. But sometimes you end up in less than ideal places and at those times a sub would be good.


Heh... agreed but I was talking about your recommendation to use UDF's. My suggestion is that Scalar and mTVF's can actually put you in one of those less than ideal places and that, with only a few exceptions, iTVF's are a better way to go.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36255 Visits: 11811
Jeff Moden (3/3/2011)
Telarian (3/3/2011)
Jeff Moden (3/3/2011)


Oh, be careful now. "It Depends". :-) Scalar and Multi-Line Table Valued Functions (mTVF) can very quickly turn code into some pretty nasty, performance challenged RBAR especially if it refers to a table. Think of it as a hidden cursor.

Inline Table Valued Functions (iTVF), however, can be VERY useful and are calculated in the execution plan as if they were a "programmable view". Oddly enough, they also work very effectively to return single values like a Scalar function would when used with CROSS APPLY (thanks to Paul White for that nice little trick).


Don't get me wrong, I'm not saying there aren't usually better ways to do things. But sometimes you end up in less than ideal places and at those times a sub would be good.


Heh... agreed but I was talking about your recommendation to use UDF's. My suggestion is that Scalar and mTVF's can actually put you in one of those less than ideal places and that, with only a few exceptions, iTVF's are a better way to go.

I try to avoid scalar functions. One of the worst performance anti-patterns I've seen are when a UDF contains an inline select for a scalar value. This is a common example where joining the customer table, a view, or TVF would be much more appropriate:

select
app.schedule_date,
app.cust_id,
dbo.get_cust_fullname( cust_id ) cust_name, -- ouch!
dbo.get_cust_phone( cust_id ) cust_phone -- ouch!
from appointments app
where app.schedule_date >= '2011-02-01';

Even for for things like calculated indicators, formatting, or concatenating a customer's first name, last name, and title into a full name, I'll implement those as columns in a view based on the table that contains the columns.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
dmeissner
dmeissner
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 19
I know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.

The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?

To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.

In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?

It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.

Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.

Dave
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)

Group: General Forum Members
Points: 278084 Visits: 42244
dmeissner - Saturday, August 12, 2017 7:07 PM
I know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.

The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?

To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.

In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?

It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.

Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.

Dave

If the op had phrased the question in such a fashion, perhaps there wouldn't have been what you call a lame dialog of ideas. As it was, the OP asked a general question either open to interpretation with an urge to get at the underlying reason for the open question or open to very short, unhelpful answers like "Submit a CONNECT item".

So, do you have an answer to the OPs question or was it your intent to actually succeed in posting something even more lame than what you've cited (and you DID succeed in grand style at that)?

To answer the OPs questions, although subroutines don't exist in SQL Server, there are a whole lot of alternatives and a good number of them don't require them to exist in any of the databases that you want to affect. But it depends on what you want to do and the OP didn't cite that purpose in the original post and so a discussion ensued. As for compelling MS to do anything, log into the MS CONNECT site, make a suggestion, and try to get people to vote it up. If MS thinks it's a lame suggestion or doesn't see the merit in doing it, they'll close the item regardless of the vote. If they don't know what to do with it, they may close it or keep it open for a decade. If they think it's a good idea, they may work on it now or in a decade and eventually put it in place sometimes with or without trickle down to previous versions.

As for your specific question as to how to check for existence of objects and possibly create or affect objections in 100 or 500 databases (whatever) on the same server, you could use the undocumented sp_MSForEachDB or roll your own little bit of dynamic sql or learn how to use temporary stored procedures with dynamic sql. All you have to do is ask a reasonable question, which includes why you want to do it so we can help you with the best of many possible solutions, or make a clarification without being a freakin' troll about it. Comprende?

Now, take the chip off your shoulder and welcome aboard! Wink


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42194 Visits: 8724
Jeff Moden - Saturday, August 12, 2017 8:16 PM
dmeissner - Saturday, August 12, 2017 7:07 PM
I know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.

The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?

To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.

In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?

It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.

Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.

Dave

If the op had phrased the question in such a fashion, perhaps there wouldn't have been what you call a lame dialog of ideas. As it was, the OP asked a general question either open to interpretation with an urge to get at the underlying reason for the open question or open to very short, unhelpful answers like "Submit a CONNECT item".

So, do you have an answer to the OPs question or was it your intent to actually succeed in posting something even more lame than what you've cited (and you DID succeed in grand style at that)?

To answer the OPs questions, although subroutines don't exist in SQL Server, there are a whole lot of alternatives and a good number of them don't require them to exist in any of the databases that you want to affect. But it depends on what you want to do and the OP didn't cite that purpose in the original post and so a discussion ensued. As for compelling MS to do anything, log into the MS CONNECT site, make a suggestion, and try to get people to vote it up. If MS thinks it's a lame suggestion or doesn't see the merit in doing it, they'll close the item regardless of the vote. If they don't know what to do with it, they may close it or keep it open for a decade. If they think it's a good idea, they may work on it now or in a decade and eventually put it in place sometimes with or without trickle down to previous versions.

As for your specific question as to how to check for existence of objects and possibly create or affect objections in 100 or 500 databases (whatever) on the same server, you could use the undocumented sp_MSForEachDB or roll your own little bit of dynamic sql or learn how to use temporary stored procedures with dynamic sql. All you have to do is ask a reasonable question, which includes why you want to do it so we can help you with the best of many possible solutions, or make a clarification without being a freakin' troll about it. Comprende?

Now, take the chip off your shoulder and welcome aboard! Wink

Well said on all counts (as usual) Jeff.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70274 Visits: 10984
TheSQLGuru - Sunday, August 13, 2017 12:11 PM
Jeff Moden - Saturday, August 12, 2017 8:16 PM
dmeissner - Saturday, August 12, 2017 7:07 PM
I know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.

The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?

To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.

In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?

It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.

Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.

Dave

If the op had phrased the question in such a fashion, perhaps there wouldn't have been what you call a lame dialog of ideas. As it was, the OP asked a general question either open to interpretation with an urge to get at the underlying reason for the open question or open to very short, unhelpful answers like "Submit a CONNECT item".

So, do you have an answer to the OPs question or was it your intent to actually succeed in posting something even more lame than what you've cited (and you DID succeed in grand style at that)?

To answer the OPs questions, although subroutines don't exist in SQL Server, there are a whole lot of alternatives and a good number of them don't require them to exist in any of the databases that you want to affect. But it depends on what you want to do and the OP didn't cite that purpose in the original post and so a discussion ensued. As for compelling MS to do anything, log into the MS CONNECT site, make a suggestion, and try to get people to vote it up. If MS thinks it's a lame suggestion or doesn't see the merit in doing it, they'll close the item regardless of the vote. If they don't know what to do with it, they may close it or keep it open for a decade. If they think it's a good idea, they may work on it now or in a decade and eventually put it in place sometimes with or without trickle down to previous versions.

As for your specific question as to how to check for existence of objects and possibly create or affect objections in 100 or 500 databases (whatever) on the same server, you could use the undocumented sp_MSForEachDB or roll your own little bit of dynamic sql or learn how to use temporary stored procedures with dynamic sql. All you have to do is ask a reasonable question, which includes why you want to do it so we can help you with the best of many possible solutions, or make a clarification without being a freakin' troll about it. Comprende?

Now, take the chip off your shoulder and welcome aboard! Wink

Well said on all counts (as usual) Jeff.

Agreed. BTW, I think procedures can still call other procedures. Wink



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
dmeissner
dmeissner
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 19
Calm down now Jeff. Don't be triggered yourself. I was only joking will the "Comprende" comment and it wasn't directed at you.

Time and time again, I run across posts where someone asks a specific question and everyone has to question the person's motives rather than answering the question. This was no exception. If there were other ways to perform the same feat, there wasn't much dialog about it.

I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution.

Anyway, isn't it great reviving a thread that's 7 years old? Must be a world record.

Take care...Dave
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)SSC Guru (278K reputation)

Group: General Forum Members
Points: 278084 Visits: 42244
dmeissner - Monday, August 14, 2017 1:16 AM
Calm down now Jeff. Don't be triggered yourself. I was only joking will the "Comprende" comment and it wasn't directed at you.

Time and time again, I run across posts where someone asks a specific question and everyone has to question the person's motives rather than answering the question. This was no exception. If there were other ways to perform the same feat, there wasn't much dialog about it.

I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution.

Anyway, isn't it great reviving a thread that's 7 years old? Must be a world record.

Take care...Dave

Heh... calm down? Ironically, I was going to suggest that you do the same. Your first post on the site and you came out with words like "lame" and suggested that many of the responses came from weak minds.

The reason people here ask questions rather than providing immediate answers is because the OP frequently has no clue of what they're asking and so folks try to get to the root problem with the OP rather than providing an immediate answer which is frequently wrong for either the actual or future usage and teaches the OP little either way.

And, no... resurrecting a 7 year old thread isn't close to a record although resurrecting one in such a thoughtless fashion may be. And speaking of triggers, consider the fact that you did exactly what you accused others of doing... you have an answer and still you didn't post it.


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dmeissner
dmeissner
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 19
Wow, triggered again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search