Temporary Stored Procedures

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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.
"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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Luis Cazares (6/25/2013)

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.

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.

--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)
Intro to Tally Tables and Functions

Ken SSCoach Points: 17069 More actions · Answer 7

I did a search online and I found very few articles discussing temp stored procs. Almost all said that a permanent stored proc is usually better. Most cases for using them were as Harsha mentions, to get around a permissions limitation. There were also a couple of developers using them to avoid adding objects to the database. Personally I would rather have the code added as a permanent stored proc so there is more transparency in regards to what is being executed. Then if the developer reaches out with a performance issue or question, you can just open the proc versus being handed some inline code that is being called in an app.

Also, just to follow up. I am thankful to Harsha for writing this article as I had no idea you could even do a temporary stored procedure. Regardless of the fact that I may never use this feature, it is good to know about.

vliet SSCommitted Points: 1998 More actions · Answer 8

Aside from 'real' temporary procedures (those with a single or double # prefix), I did found a few occasions where procedures on other databases were stored in [tempdb]. In those cases procedures were stored there because it was the only database where users had permission to create objects. This leads to a potentially hazardous situation because on most instances no backups are made for [tempdb]. If these stored procedures are used (for example) as a data source for an Excel grid, things get pretty nasty after a SQL server restart. Scripts to recreate these stored procedures (that should not reside there in the first place), if they still exists, are scattered throughout your organization. So a check on semipermanent objects residing in [tempdb] should ring some alarm bells to avoid unpleasant surprises after a restart.

Roland Howard Boorman SSC-Addicted Points: 408 More actions · Answer 9

Roland Howard Boorman

SSC-Addicted

Points: 408

June 26, 2013 at 4:18 am

#1627456

Why would you want to write billions of lines of Exec Code?

Decoding that is utter madness.

Used carefuly and thoughtfully it avoids precisely sphagetti Code and supplies a simply elegant solution.

Especially when used with temporary tables.

mtassin SSC-Insane Points: 23099 More actions · Answer 10

Roland Howard Boorman (6/26/2013)

Why would you want to write billions of lines of Exec Code?

Decoding that is utter madness.

Used carefuly and thoughtfully it avoids precisely sphagetti Code and supplies a simply elegant solution.

Especially when used with temporary tables.

And debugging what is in essence dynamic SQL just stuck in a temp stored proc vs using sp_executesql is any different?

How do you create said stored proc from within a stored proc? Oh right, probably dynamic sql. So you write a dynamic SQL generator, that could have run the code via sp_executesql, but instead have it create a temp stored procedure and execute that?

I've written stored procs that make stored procs before, sometimes temporary, and then execute them, but that was before I learned more about exec sp_executesql, say 14 years ago. I'm in Jeff's camp about this, I don't see any benefits these bring to you that you can't achieve with sp_executesql and an nvarchar(max) variable.

In fact, debugging the dynamic code is generally easier, because a print @variable command can be slipped into the stored proc to see what the dynamic code is that's being generated, and while you could do that with the temp stored proc too, you're still then stuck with a create #proc statement, which has to then be created and then executed.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Roland Howard Boorman SSC-Addicted Points: 408 More actions · Answer 11

You still don't get it. It is to simplfy and actually make the logic simpler.

In serious code the level of duplication and associated complexity becomes absurb especially when dealing with rich User interfaces. My background was C++ and Java with Javascripting.

I was horrified at the absurb development of middleware essentially to manage databases.

The reality was logic spread between front end, middle ware and SQL. So you look for elegant processing and tight user interfaces.

If you live in the SQL world without any knowledge of middleware or frontend you are 10% of the developer you should be. As for all the do this and don't do that a look a the Microsoft supplied functions and procs blows these out of the water. Just because they are ued and referenced without needing to look below the surface you are living in a surreal world.

I devloped techniques which can move solutions between SQL an MySQL and using XML and temp Procs. Rich complexity but with beautiful clear logic.

danielk1 SSC-Addicted Points: 462 More actions · Answer 12

I have to agree with the descent here:

If I am going to write something "temporary" then it is really just TSQL not a SP. A SP is permanent construct of the DB. There may be a thing or two on the fringe you cannot do in TSQL and it must be in a SP, but I cannot think of anything day-to-day where it would require to be inside a SP.

Roland Howard Boorman SSC-Addicted Points: 408 More actions · Answer 13

Roland Howard Boorman

SSC-Addicted

Points: 408

June 26, 2013 at 1:13 pm

#1627694

The use of temporary Procs. Of course these are written is TSQL but there purpose is to reduce complexity and at the same time handle specialised and particular sessions.

I understand the benefits of polymorphism and temporary Procs can actually create the ladders of Functions and data associated with Object Databases.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Roland Howard Boorman (6/26/2013)

You still don't get it. It is to simplfy and actually make the logic simpler.

In serious code the level of duplication and associated complexity becomes absurb especially when dealing with rich User interfaces. My background was C++ and Java with Javascripting.

I was horrified at the absurb development of middleware essentially to manage databases.

The reality was logic spread between front end, middle ware and SQL. So you look for elegant processing and tight user interfaces.

If you live in the SQL world without any knowledge of middleware or frontend you are 10% of the developer you should be. As for all the do this and don't do that a look a the Microsoft supplied functions and procs blows these out of the water. Just because they are ued and referenced without needing to look below the surface you are living in a surreal world.

I devloped techniques which can move solutions between SQL an MySQL and using XML and temp Procs. Rich complexity but with beautiful clear logic.

I'm asking for an actual example. You sound like you've use TSPs a lot and you should be able to provide an actual example of how they were used and what the actual benefits are instead of relying on rhetoric. I believe that everyone would appreciate such an example delivered in a calm and thoughtful manner instead of implying that someone is only "10% of the developer you should be".

For example, one of the TSPs in the article simply does an insert statement using hardcoded values. It such a thing useful in simplifying code? I'm not asking as a point of contention. I'd really like to know one way or the other. To be sure, I'd love to find out what you actually use them for and why they're better. Right now, I'm not seeing it but that's what the disussion for such articles is for... to learn something new. 😉

Do you have an actual example that you'd care to share? It could really help all of us learn a new trick.

--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)
Intro to Tally Tables and Functions

Roland Howard Boorman SSC-Addicted Points: 408 More actions · Answer 15

Right haven't had time to check run this and its old code but the purpose is to show an elegant solution to what as originally coded was awful and bug ridden

There is an external table that is to be populated called #Cell_Matrix

The coder has NO idea of what the user range of selection or even different source tables but is required to populate this #Cell_Matrix table.

Its starts by invoking an stored procedure (a sub store procedure )

Then sets up counters because the will be a process of iteration depending on whatever?

Notice how the temp proc creates a temporary table that intefaces with a temp table created within the stored proc #CounterTlb and inserts results into the external table #Cell_Matrix

The iteration is simply and everything temporary is dropped and it avoids a nightmare of coding

CREATE PROCEDURE sp_report_Second_D

AS

EXEC sp_REPORT_second

DECLARE @xCounter integer

DECLARE @MaxId integer

CREATE TABLE #CounterTbl (

[ID] [int] IDENTITY(1,1) NOT NULL,

[GROUPCODE] [nvarchar](10) DEFAULT ('')

)

DECLARE @Counter INTEGER

SET @Counter = 1000

INSERT INTO #CounterTbl (GROUPCODE) VALUES('1')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('2')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('3')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('4')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('5')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('6')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('7')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('8')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('9')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('10')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('11')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('12')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('13')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('14')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('15')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('16')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('17')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('18')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('99')

INSERT INTO #CounterTbl (GROUPCODE) VALUES('100')

SET @xCounter = 1

SELECT @MaxId = count(id) FROM #CounterTbl

EXEC (' CREATE PROC #X ( @xCounter int, @Counter int )

AS

DECLARE @GROUPCODE INTEGER

DECLARE @D_GROUP VARCHAR(100)

DECLARE @h INTEGER

DECLARE @m INTEGER

DECLARE @O INTEGER

DECLARE @b-2 INTEGER

DECLARE @a INTEGER

DECLARE @C INTEGER

DECLARE @CR INTEGER

DECLARE @HA INTEGER

DECLARE @E INTEGER

DECLARE @CA INTEGER

DECLARE @SO INTEGER

DECLARE @BA INTEGER

DECLARE @MA INTEGER

DECLARE @an INTEGER

DECLARE @ALL INTEGER

DECLARE @OT INTEGER

DECLARE @PO INTEGER

DECLARE @PR INTEGER

DECLARE @MI INTEGER

DECLARE @NA INTEGER

DECLARE @Total INTEGER

DECLARE @Total integer

SELECT

@GROUPCODE=GR.[GROUPCODE],

@D_GROUP=[D_GROUP],

@h=[H],

@m=[M],

@O=[O],

@b-2=,

@a=[A (EXCLUDING E)],

@C=[C (EXCLUDING C)],

@CR=[CR],

@HA=[HA],

@E=[E],

@CA=[CA],

@SO=[SO],

@BA=[BA],

@MA=[MA],

@an=[AN],

@ALL=[ALL],

@OT=[OT],

@PO=[PO],

@PR=[PR],

@MI=[MI],

@NA=[N/A],

@Total=[Total]

from #aD

as GR JOIN #CounterTbl as dr ON dr.GROUPCODE=GR.GROUPCODE

WHERE DR.ID= @Counter

INSERT INTO #Cell_Matrix

SELECT @Counter, @h

INSERT INTO #Cell_Matrix

SELECT @Counter + 1 , @m

INSERT INTO #Cell_Matrix

SELECT @Counter + 2 , @O

INSERT INTO #Cell_Matrix

SELECT @Counter + 3 , @b-2

INSERT INTO #Cell_Matrix

SELECT @Counter + 4 , @a

INSERT INTO #Cell_Matrix

SELECT @Counter + 5 , @C

INSERT INTO #Cell_Matrix

SELECT @Counter + 6 , @CR

INSERT INTO #Cell_Matrix

SELECT @Counter + 7 , @h

INSERT INTO #Cell_Matrix

SELECT @Counter + 8 , @E

INSERT INTO #Cell_Matrix

SELECT @Counter + 9 , @CA

INSERT INTO #Cell_Matrix

SELECT @Counter + 10 , @SO

INSERT INTO #Cell_Matrix

SELECT @Counter + 11 , @BA

INSERT INTO #Cell_Matrix

SELECT @Counter + 12 , @MA

INSERT INTO #Cell_Matrix

SELECT @Counter + 13 , @an

INSERT INTO #Cell_Matrix

SELECT @Counter + 14 , @ALL

INSERT INTO #Cell_Matrix

SELECT @Counter + 15 , @OT

INSERT INTO #Cell_Matrix

SELECT @Counter + 16 , @PO

INSERT INTO #Cell_Matrix

SELECT @Counter + 17 , @PR

INSERT INTO #Cell_Matrix

SELECT @Counter + 18 , @MI

INSERT INTO #Cell_Matrix

SELECT @Counter + 19 , @NA

INSERT INTO #Cell_Matrix

SELECT @Counter + 20 , @Total

GO

')

Table_Loop:

IF @maxid >= @xCounter

BEGIN

EXEC #X @xCounter, @Counter

SET @Counter = @Counter + 21

SET @xCounter = @xCounter +1

GOTO Table_Loop

END

DROP PROC #X

GO