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:00 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
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."

(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 #1467435
Posted Tuesday, June 25, 2013 10:35 PM


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: Friday, August 22, 2014 2:00 PM
Points: 3,293, Visits: 1,976
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.
Post #1467446
Posted Wednesday, June 26, 2013 4:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:19 PM
Points: 67, Visits: 426
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.
Post #1467546
Posted Wednesday, June 26, 2013 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
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.
Post #1467547
Posted Wednesday, June 26, 2013 7: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: Friday, August 22, 2014 7:04 AM
Points: 3,673, Visits: 72,433
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
For tips on how to post your problems
Post #1467658
Posted Wednesday, June 26, 2013 10:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
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.
Post #1467783
Posted Wednesday, June 26, 2013 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 9:54 AM
Points: 22, Visits: 148
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.
Post #1467798
Posted Wednesday, June 26, 2013 1:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
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.
Post #1467828
Posted Wednesday, June 26, 2013 2:52 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
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."

(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 #1467877
Posted Wednesday, June 26, 2013 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
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 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=[B],
@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

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

Post #1467899
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse