Temporary Stored Procedures

Question

Temporary Stored Procedures

Viewing 15 posts - 31 through 45 (of 62 total)

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

john.arnott SSChampion Points: 11882 More actions · Answer 1

Jeff,

I'm a developer who has read access to our production databases, but not object-creation access. For any on-going programs, naturally we'd test them in development, then get the DBA to install a stored procedure. But when we get a request for an ad-hoc report for our business partners, I can imagine where being able to use a temp sp may allow for an easier or more efficient solution than depending on CTE's or other techniques. I wasn't aware of the possibility until I read this article, and have not yet had occasion to use the technique, but don't understand your apparently strong aversion to this.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

I'm not sure is an aversion from Jeff towards the TempSPs, but more of a "let's learn together". I've heard of the TSPs before but had never found any real use. I mentioned that the article showed acceptable scenarios to use them but, in the end, I might never use them.

They're an interesting tool, but is it the best for something? When they told me about PIVOT on SQL Server I thought it would be very useful, but I continue to use cross tab queries. Same with the TSPs, it's good to know they exist and how do they work but we need to be sure is the best option before using them for everything.

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

john.arnott (6/26/2013)
Jeff,
I'm a developer who has read access to our production databases, but not object-creation access. For any on-going programs, naturally we'd test them in development, then get the DBA to install a stored procedure. But when we get a request for an ad-hoc report for our business partners, I can imagine where being able to use a temp sp may allow for an easier or more efficient solution than depending on CTE's or other techniques. I wasn't aware of the possibility until I read this article, and have not yet had occasion to use the technique, but don't understand your apparently strong aversion to this.

Luis Cazares, good man that he is, hit the nail on the head. The only reasons why I currently have what folks are calling an "aversion" to TSPs are:

1. As I mentioned, I've worked with folks that pretty much screwed things up using TSPs because they used them to avoid code reviews and ended up with horrible RBAR solutions. It's a human failure on my part to react to some things like this but it's still in the back of my mind. HOWEVER, that's definitely NOT to say that there aren't good reasons to use them BUT that brings us to part 2...

2. I would hope the examples in the article aren't actual examples that anyone has used. I DO understand that they were simple examples to show the functionality of being able to use a TSP without the complexity of showing what you could actually use them for. That's the whole problem so far, though. Lot's of folks have admitted and (sometimes rudely) admonished how useful they are but have done so without a clear example. Saying things like they've used them for XML or whatever isn't a clearly defined example. I'm a hard core data troll and I need to see an actual example of the code for a TSP and an explanation of why other methods could not have been used instead.

With that understanding, all I'm asking for is a reasonable coded example.

Back to your suggestion of "I can imagine where being able to use a temp sp may allow for ..." I have to ask... "Ok, so where are you running the script from?" That's important because such ad hoc requirements rarely require the reuse of code within the code unless it's RBAR in nature to begin with. Since you've only just become aware of TSPs, I won't hold you to a coded example but what would you "imagine" that you could actually use a TSP for since you're not able to write an actual stored procedure? And why wouldn't just a script (dynamic or not) work just as well?

Again, I'm not asking as a challenge... I'm asking because I'd like to know and I'm sure that people reading this thread would benefit, as well. I love having the "Oh... Yeah! I GET IT!" moment when I can not only understand the example but start imagining clear examples of where I'd use it on my own.

BTW, hat's off to you for your honesty when it comes to the proper testing and promotion of stored procedures (although I could tell that from your fine posts over time). It would be an honor to work with you and Luis both.

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

Luis Cazares (6/26/2013)
I'm not sure is an aversion from Jeff towards the TempSPs, but more of a "let's learn together". I've heard of the TSPs before but had never found any real use. I mentioned that the article showed acceptable scenarios to use them but, in the end, I might never use them.
They're an interesting tool, but is it the best for something? When they told me about PIVOT on SQL Server I thought it would be very useful, but I continue to use cross tab queries. Same with the TSPs, it's good to know they exist and how do they work but we need to be sure is the best option before using them for everything.

Absolutely spot on, Luiz. Remind me to add "mind reader" and "diplomat" to your obvious list of skills. 🙂

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

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

Roland Howard Boorman (6/26/2013)
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

Thank you for the example but I'd have to say that "elegant" is in the eyes of the beholder. That code is incredibly similar to the code I was talking about and you've made my point... It's RBAR on steroids and the TSP did anything but help that. In fact, it may have been the very cause of the RBAR thinking and result.

Just to prove my point that there's a better way than using dynamic SQL, a TSP, multiple counters, not to mention the extreme RBAR, and to respect the fact that you stated that the code is "old code", I wrote the following code as if all I had was SQL Server 2000 available. It might even work on SQL Server 7. To be sure, someone smarter than me might be able to come up with an even simpler method to avoid all the RBAR of the original code.

Of course, nowadays, I'd use UNPIVOT or CROSS APPLY to unpivot the data and I'd use ROW_NUMBER() to number the rows but, like I said, I wanted to show how to avoid TSPs and the RBAR they typically are used for even way back when. In 2005 and up, this would be very short, totally set based child's play that very likely could be done without the use of any Temp Tables using a single query.

The code, of course, is untested simply because I don't have your data to test with. I'll also point out that the TSP you created selected 2 columns that weren't used for anything anywhere. I've not included those columns in the code below.

CREATE PROCEDURE dbo.sp_report_Second_D

AS

EXEC dbo.sp_REPORT_second

--===== Create and populate the GroupCode "driver" table.

-- Unfortunately, this lives only in this stored procedure

-- and can't be reused by anyone else. They'd have to write

-- identical code in their procs which becomes a bit of a

-- maintenance nightmare for additions of new group codes.

-- Still we were able to change the overhead of 20 INSERTs

-- into a single insert to save on performance and resources.

-- Since we no longer need the dynamic SQL to build the TSP

-- with, I changed the datatype of the GroupCode column to

-- match what's in the #Cell_Matrix table.

CREATE TABLE #GroupCode

(

GroupCodeID INT IDENTITY(1,1) NOT NULL,

GroupCode INT NOT NULL

)

;

INSERT INTO #GroupCode

(GroupCode)

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12 UNION ALL

SELECT 13 UNION ALL

SELECT 14 UNION ALL

SELECT 15 UNION ALL

SELECT 16 UNION ALL

SELECT 17 UNION ALL

SELECT 18 UNION ALL

SELECT 99 UNION ALL

SELECT 100

;

--===== I have no idea why this is hardcoded but continued to use it to meet the

-- requirements of the existing code.

DECLARE @Counter INTEGER;

SET @Counter = 1000;

--===== Create and populate the staging table to provide the final "counter" numbering

-- to the #Cell_Matrix table. We'll add the value of @Counter to the Counter

-- column as we insert into the #Cell_Matrix table near the end of this script.

CREATE TABLE #Staging (Counter INT IDENTITY(0,1), UnPivotValue INT);

INSERT INTO #Staging

SELECT UnPivotValue = d.UnpivotValue

FROM ( --=== This does an "old fashioned unpivot" of the data (CBAC but still better than RBAR)

SELECT gc.GroupCodeID, 1, ad.H FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 2, ad.M FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 3, ad.O FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 4, ad.B FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 5, ad.[A (EXCLUDING E)] FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 6, ad.[C (EXCLUDING C)] FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 7, ad.CR FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 8, ad.HA FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 9, ad.E FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 10, ad.CA FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 11, ad.SO FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 12, ad.BA FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 13, ad.MA FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 14, ad.AN FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 15, ad.[ALL] FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 16, ad.OT FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 17, ad.PO FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 18, ad.PR FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 19, ad.MI FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 20, ad.[N/A] FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode UNION ALL

SELECT gc.GroupCodeID, 21, ad.Total FROM #aD AS ad JOIN #GroupCode AS gc ON gc.GroupCode = gr.GroupCode

) d (GroupCodeID, ColNum, UnpivotValue)

ORDER BY d.GroupCodeID, d.ColNum

;

--===== All set. Do the final insert into the #Cell_Matrix with the correct "counter" values.

INSERT INTO #Cell_Matrix --This really should have a "column list" but don't know schema of #Cell_Matrix

SELECT Counter + @Counter, UnpivotValue

FROM #Staging

ORDER BY Counter --This shouldn't really be needed but included to simulate the order of the loop.

;

The TSP was not only not necessary, but its use actually made for some pretty heavy RBAR. Even using really old code methods, its totally unnecessary for all the RBAR that both surrounds and is contained in the TSP.

I also hope that you've broken the nasty habit of naming things with the "sp_" prefix which can have a terrible effect on performance and has been clearly identified by Microsoft as a worst practice for decades now.

Does anyone have a practical example of what you might want to use a TSP for in a post-2000 world? And, no, I'm not trying to be argumentative. I just can't think of a practical example of what to use a TSP for and would love to learn one.

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

Mr.DiGi SSC Enthusiast Points: 105 More actions · Answer 6

Mr.DiGi

SSC Enthusiast

Points: 105

June 27, 2013 at 2:05 am

#1627832

Well, this is dafuq of day... :w00t:

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

I wear one cap for Creating, Changing and Updating Data which should be rigorious documented and regulated.

However businesses need to be flexible over analysis, presentation and using the rich data that they hold to achieve business goals.

Its this use of data where we findwhat I call hacked code bolted together harded to maintain and poorly documented. Now I developed over the years an approach to this problem using XML , Functions, temporary tables and stored Procs. But in some cases (the example shows an iteration using a temporary proc in a loop ) this will give good documented code that is easy to maintain and yet flexible enough meet changes.

We need to be aware of the business need, the protection of data and the need to produce well documented code.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 8

cschlieve (6/25/2013)
How would you use them. I cannot think of a case where this would be useful?

Global Temporary Stored Procedures are very helpful when testing updated code in Production, prior to releasing that code. Assuming that it is SELECT only, you can do A / B testing in Production (where ideally we cannot just create objects willy-nilly) to make sure performance and/or logic fixes actually work with the real data set(s). By using Global Temp Procs you can easily open multiple sessions and have one tab for the CREATE / ALTER ##Proc (for easy edits to the code while testing), one tab for running the ##Proc, and another tab for running the current Production code.

Take care,

Solomon...

Edit:

I forgot to mention that in order to get a true apples-to-apples comparison on new vs current code, you also need to make a copy of the current code as a Global Temporary Stored Procedure. You now have two Global Temp Procs to EXEC and the execution of the "current" code temp proc will neither impact, nor be impacted by, the actual current code proc (i.e. cached query plan).

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

theboyholty Ten Centuries Points: 1050 More actions · Answer 9

Purely in the interests of throwing the cat amongts the pigeons, you can write a line of code in a stored proc to drop itself you know. I know it doesn't exactly move the discussion along but I found it interesting that a self sacrificial stored procedure such as the one below actually works.

create proc usp_suicide_proc

as

begin

print 'goodbye cruel world, its been emotional'

drop proc usp_suicide_proc

end

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

Thank you Jeff for looking at the code and I like your solution.

This is Old code and is just an example that code interface was driven from within Excel with VBA and SQL backend. The Grid_Table was Cell references with specific data being returned (various types). The User could select from a series of datasets over several years and or by month. The Application generated up to 80 individual outlet results (same model) with Dashboards and X tabs all generated.

The user requests blocked in XML and the returned Grid_table populating the various models.

The App used worksheet templates and built up WorkBooks dynamically for each outlet.

One of the problems was the original code (written as per your aproach was it ran to slow and would time out. Microsoft have built the macros for Pivot etc but they are course using dynamic code behind these!

You can beat the # and ## temps for speed.

That code runs like lighting.

The App used a single Proc interface (only one there was no other route permitted) The XML used to pass complex User Data details and requirements. These treated as a Table. The single Proc interface off loaded the requests to various sub Procs and then returned the datasets back to Excel.

Audit tracking was automatically built into and recorded within the DataBase.

This approach easily facilitates replacing the frontend with a Browser interface and Javascripting. Json replacing XML and Datasets is straightfoward.

Microsoft uses these temorary tools themselves.

The Microsoft PIVOT and UNPIVOT can only work using dynamic code if you think about it! But there's is a generic approach an sometimes you can write better code yourself.

Notice this isn't hidden code on the contrary the client could see the logic and maintain it for themselves.

Objects and classes with Functions and Procs is a better approach to SQL interface (IMO).

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

Roland Howard Boorman (6/27/2013)
The Microsoft PIVOT and UNPIVOT can only work using dynamic code if you think about it!

I'm not sure how you think so. It does, in fact, take a fair bit of effort to actually make it dynamic.

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

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

Pivot as microsoft have developed it is dynamic. It has to be because it needs to create a list of the fields. You don't see this But it is there. These temp proc , tables etc.. exist because they were used as part of developing the SQL language. Its the way that the tables are created in SQL they are hardwired.

I started earlier on by pointing out that Microsoft use these in their own MACROS and Functions especially dynamic code.

I think that much of the middleware and processing should be at the SQL level! You protect access and make this very restricted ( I tend to use one Proc as an entry point) and pass XML data which is then processed and drives the todo's. The XML will have been created from User interface selection and will be precise as to what is required and what is returned. Basically store the key data as from away from the front end as possible and never with express direct user rights! Always building in Audit! Ever time the proc is used the user and request is stored with time and date!

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 13

theboyholty (6/27/2013)
That code runs like lighting.

Maybe compared to the original code but with all of the RBAR you have in the code you posted, "lightning" isn't the word I was thinking of.

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

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

Roland Howard Boorman (6/27/2013)
I wear one cap for Creating, Changing and Updating Data which should be rigorious documented and regulated.
However businesses need to be flexible over analysis, presentation and using the rich data that they hold to achieve business goals.
Its this use of data where we findwhat I call hacked code bolted together harded to maintain and poorly documented. Now I developed over the years an approach to this problem using XML , Functions, temporary tables and stored Procs. But in some cases (the example shows an iteration using a temporary proc in a loop ) this will give good documented code that is easy to maintain and yet flexible enough meet changes.
We need to be aware of the business need, the protection of data and the need to produce well documented code.

What does any of that have to do with the use TSPs? And, BWAA-HAAA!!!!.... speaking of "well documented code"... what happened to that notion on the code that you provided as an example TSP?

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 15

theboyholty (6/27/2013)
Purely in the interests of throwing the cat amongts the pigeons, you can write a line of code in a stored proc to drop itself you know. I know it doesn't exactly move the discussion along but I found it interesting that a self sacrificial stored procedure such as the one below actually works.
create proc usp_suicide_proc
as
begin
print 'goodbye cruel world, its been emotional'
drop proc usp_suicide_proc
end

I'm actually glad you brought this up. I've not done such a thing with stored procedures but have done such a thing with SQL Jobs (self deleting jobs). Such jobs are truly "ad hoc" jobs that need to be executed at a particular time of day and will never be reused as written ever again. It's a bit like usig WAIT FOR TIME except the advantage is that I don't need to keep my machine powered up. It's all in the hands of the server.

Something to be aware of, I've also seen demonstrations by hackers who use the technique to get in and get out without leaving a trace.

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