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

Well said on all counts (as usual) Jeff.