Temporary Stored Procedures

  • Comments posted to this topic are about the item Temporary Stored Procedures

  • I always forget this feature 😀

  • I always forget it too - but only at the end of the session

  • geoff-635333 (6/25/2013)


    I always forget it too - but only at the end of the session

    🙂

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • I wonder how many people use them?

  • Use what?

    🙂

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • I have successfully used temporary Stored Procs for Years.

    They are very powerful features.

  • How would you use them. I cannot think of a case where this would be useful?

  • Hmmm...I had no idea this was possible. The concept completely slipped by me! I learned something new today. Thanks!

    As for usefulness...I saw the potential as soon as I read this article. Too many times I've had to create separate procedures or functions just for the purpose of simplifing my code that are only relevant to the proc they run in due to hard-coded values, etc. Temp procedures will encapsulate that code within the procedure itself without adding another variation to maintain. I will be able to use this right now on some procedures I'm working on.

     

  • In Oracle you can create "local" procedures that are only available to the current process being executed. The local procedure keeps you from have to duplicate code sections that you need to repeatedly invoke. My understanding is that a temporary Stored procedure would serve the same purpose. IMHO, leave it to Microsoft to take something simple and obfuscate it.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • You can also use with encryption when creating the temp stored procedure.

    Interestingly in SQL 2012 when the encrypted stored procedure is created the SQL code is viewable in profiler, unlike previous SQL versions... A bug??

  • any sample of using this temp sproc?

    i usually use dynamic query for defining same complex processes from multiple tables.

    i wonder how to create temp sproc inside a procedure or it is just for ad hoc process in SSMS?

  • You're encrypting the temp stored procedure?? I am not clear what you are saying you "can" do.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • This was interesting. Thanks for writing this article.

    I do slightly disagree with one part of what you've said which is that SQL doesn't support temporary views. While this is technically correct, SQL Server's CTE functionality does a pretty good impersonation of temporary views.

    Thanks again.

  • All of those asking for cases on when Temp Stored Procedures would be useful ever read the entire article? At the end, it mentions some acceptable scenarios.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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