June 25, 2013 at 11:04 am
I would just use in line TSQL with a begin/end and declaration of vars - no need to have a SP
June 25, 2013 at 12:39 pm
I have used temporary Stored Procs for many years with XML and inline code they are a very elegant wen used with temporary tables
June 25, 2013 at 1:42 pm
Is it just me, or does this sound like an auditing nightmare?
Mattie
June 25, 2013 at 7:06 pm
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.
June 25, 2013 at 8:21 pm
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
Change is inevitable... Change for the better is not.
June 25, 2013 at 10:00 pm
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
Change is inevitable... Change for the better is not.
June 25, 2013 at 10:35 pm
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.
June 26, 2013 at 4:15 am
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.
June 26, 2013 at 4:18 am
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.
June 26, 2013 at 7:42 am
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.
June 26, 2013 at 10:57 am
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.
June 26, 2013 at 11:26 am
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.
June 26, 2013 at 1:13 pm
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.
June 26, 2013 at 2:52 pm
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
Change is inevitable... Change for the better is not.
June 26, 2013 at 4:09 pm
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
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply