Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Temporary Stored Procedures Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 10:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:25 AM
Points: 10, Visits: 380
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??
Post #1467262
Posted Tuesday, June 25, 2013 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:37 AM
Points: 12, Visits: 268
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?
Post #1467263
Posted Tuesday, June 25, 2013 10:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1467264
Posted Tuesday, June 25, 2013 10:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 135, Visits: 323
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.
Post #1467279
Posted Tuesday, June 25, 2013 10:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1467286
Posted Tuesday, June 25, 2013 11:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 9:54 AM
Points: 22, Visits: 148
I would just use in line TSQL with a begin/end and declaration of vars - no need to have a SP
Post #1467292
Posted Tuesday, June 25, 2013 12:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
I have used temporary Stored Procs for many years with XML and inline code they are a very elegant wen used with temporary tables
Post #1467319
Posted Tuesday, June 25, 2013 1:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:46 AM
Points: 2,606, Visits: 785
Is it just me, or does this sound like an auditing nightmare?

Mattie



Post #1467358
Posted Tuesday, June 25, 2013 7:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 PM
Points: 1,521, Visits: 3,039
MattieNH (6/25/2013)
Is it just me, or does this sound like an auditing nightmare?

Mattie

How so? The temp sp goes away at the end of the session and I would presume that this feature doesn't open any back doors to allow more access than the user would have in a more direct script.

I'm happy to learn of this -- as the author suggests, it could be very useful in ad-hoc ETL situations and for testing where my DBA has (rightfully so) denied me object creation access.
Post #1467415
Posted Tuesday, June 25, 2013 8:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
Roland Howard Boorman (6/25/2013)
I have used temporary Stored Procs for many years with XML and inline code they are a very elegant wen used with temporary tables


What were the temporary stored procedures doing and why wouldn't permanent stored procedures have done the same job for you?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1467419
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse