Post reply

Temporary Stored Procedures

  • June 24, 2013 at 10:15 pm

    #278964

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

  • June 25, 2013 at 3:47 am

    #1627039

    I always forget this feature 😀

  • June 25, 2013 at 5:14 am

    #1627057

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

  • June 25, 2013 at 7:49 am

    #1627119

    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.

  • June 25, 2013 at 8:04 am

    #1627131

    I wonder how many people use them?

  • June 25, 2013 at 8:07 am

    #1627135

    Use what?

    🙂

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

  • June 25, 2013 at 9:17 am

    #1627183

    I have successfully used temporary Stored Procs for Years.

    They are very powerful features.

  • June 25, 2013 at 9:23 am

    #1627187

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

  • June 25, 2013 at 9:53 am

    #1627205

    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.

     

  • June 25, 2013 at 9:58 am

    #1627207

    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.

  • June 25, 2013 at 10:06 am

    #1627211

    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??

  • June 25, 2013 at 10:09 am

    #1627212

    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?

  • June 25, 2013 at 10:09 am

    #1627213

    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.

  • June 25, 2013 at 10:33 am

    #1627227

    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.

  • June 25, 2013 at 10:42 am

    #1627233

    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 63 total)

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