Temporary Stored Procedures

Question

Post reply

Temporary Stored Procedures

Viewing 4 posts - 61 through 63 (of 63 total)

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

INCREDIBLEmouse SSC Eights! Points: 835 More actions · Answer 1

geoff-635333 (6/25/2013)
I always forget it too - but only at the end of the session

+1

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Jeff Moden (5/9/2015)
sanjarani (5/9/2015)
One of many SQL Servers features that is hardly ever used because alternative methods are more suitable.
Please explain your "alternative methods" and how they are "more suitable".

What is it with you folks that drop a bomb like this and then nothing after that?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Graeme Martin Old Hand Points: 324 More actions · Answer 3

Hi, Jeff--

The one place I actually need this is when a procedure has not yet been deployed to Production, and a customer wants the output it produces. Example: sales details over two years by day in JSON format (one file per day).

One way around this is to simply restore Production to a staging server, deploy the latest release and execute the stored procedure.

But if that is not an option, a temp stored procedure could be helpful.

Graeme

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 4

I have never used temporary sprocs, in fact I didn't know they existed <g>.

But to answer "when would they be useful", for you (Jeff) and danielk1, I thought there was one point in this article that seemed useful:

If I'm writing a large chunk of code as part of a deployment and if that code has to be used multiple times during the deployment but I don't want it to hang around afterwards, I can see using a temporary sproc. No, Jeff, not "multiple times" for RBAR, but let's say there is some schema adjustment that I need to make against multiple tables or maybe against multiple databases?

If it was code that needed to run during every deployment that would be different, but this would be a one time usage, and I wouldn't want to have that large chunk of code repeated directly in the deployment script, both to ensure I didn't fat finger it one time and for clarity. I can think of one role I played in the distant past, where multi-tenancy was accomplished with several hundred client databases all with the same structure, and where I was handing off deployment responsibilities for Production schema changes and related data manipulation, where I would have liked to have done this.

The same article also mentions using it in SSIS scripts if you don't have rights to create stored procedures elsewhere. That sounds fishy to me. And (in the same section, enticingly entitled "Uses of Temporary Stored procedures") he offers some other potential uses that didn't ring a bell, but maybe somebody else will find something that appeals to them?

>L<