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 Wednesday, June 26, 2013 6:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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.
Post #1467911
Posted Wednesday, June 26, 2013 7:41 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: Today @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1467922
Posted Wednesday, June 26, 2013 8:47 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(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 #1467935
Posted Wednesday, June 26, 2013 8:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(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 #1467936
Posted Thursday, June 27, 2013 12:27 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(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 #1467969
Posted Thursday, June 27, 2013 2:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:12 AM
Points: 3, Visits: 100
Well, this is dafuq of day...
Post #1467995
Posted Thursday, June 27, 2013 2:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:58 AM
Points: 354, Visits: 1,869
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# - http://www.SQLsharp.com/
Post #1468130
Posted Thursday, June 27, 2013 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 12:57 AM
Points: 112, Visits: 88
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


Post #1468224
Posted Thursday, June 27, 2013 9:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 2:03 AM
Points: 12, Visits: 45
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).
Post #1468229
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse