Yes, but why would it be better to use Temporary Stored Procedures over real stored procedures for any of those scenarios? And the one about the user not having privs to make a proc is downright spooky and provides just another method for getting around the rules that a company wants to enforce.

If you also consider that no one has mentioned such things as possible SQL Injection during the creation of the stored procedure nor the fact that you have to redeploy a package or managed code to change such a Temporary Stored Procedure (there goes the advantage of working around the system), I'm thinking that the use of Temporary Stored Procedures has lost several points in those areas, as well.

I've seen some people write Temporary Stored Procedures to support supposed code reuse. As it turned out, the code was the same as all of the examples given for this article. It was a convenient way to write some RBAR and avoid scrutiny by the DBAs.

I used a Temporary Stored Procedure in code just one time (1997 IIRC, Version 7) to overcome the 4k limit of sp_ExecuteSQL in a pre-2005 environment and did so only because my employer insisted that the code be dynamic, even though it didn't need to be IMHO, and that I absolutely had to use sp_ExecuteSQL . In this post-2005 world and with the advent of the MAX data types and the fact that sp_ExecuteSQL can now handle a billion characters, I can't immediately think of a practical use of Temporary Stored Procedures where some other method wouldn't be better other than the obvious avoidance of company policy, which I'd run a developer or contractor through a knothole for doing.

I've even helped some other folks use Temporary Stored Procedures but only because they wanted to and not because it was the best thing to do. One example was to supposedly reduce clutter because the only use of certain stored procedures was by a single other stored procedure. I just couldn't take that as a practical use since it violates the whole idea of code reuse to begin with.

So, even though it's a slick trick (and a very old one at that) I join a part of the crowd in asking the question, what would you actually use Temporary Stored Procedure for where there wasn't a better method?

And, no, not trying to attack or be difficult on this one. I'd really like to know because I just can't think of where I'd actually use it anymore because there are so many other better ways to accomplish the same thing.