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