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 Thursday, June 27, 2013 9:49 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1468379
Posted Thursday, June 27, 2013 4:32 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1468381
Posted Thursday, June 27, 2013 4:38 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1468383
Posted Thursday, June 27, 2013 4:49 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Mr.DiGi (6/27/2013)
Well, this is dafuq of day...


How so?


--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 #1468386
Posted Thursday, June 27, 2013 4:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Solomon Rutzky (6/27/2013)
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.


By jove, I believe you've found a good use for TSPs, Solomon. Frequently, Development and QA environments don't have the same volume of data that Production has. If you're careful to make sure there are no server-killing accidental many-to-many joins as well as taking other common sense precautions associated with "developing or testing in production", this is a great way to do a little testing and performance checking prior to a release. I'd strongly recommend that Developers make sure the DBA knows so that (s)he can quickly respond if one of the TSPs under test drag the server down but this sounds like the only good use for TSPs. Thanks for the post!


--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 #1468388
Posted Monday, July 1, 2013 9:03 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
Jeff Moden (6/27/2013)
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.



Now if you look carefully, Jeff, you'll see that I said the code runs like 'lighting', no mention of lightning. I was referring to those strip lights that can take about a minute to get going.
Post #1469104
Posted Friday, July 19, 2013 5:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 594, Visits: 3,493
Solomon Rutzky (6/27/2013)
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).


since hearing about Temporary Procs, i too was having trouble working out why you would use them.
This is the best example that i've seen that describes a reason to use them
Post #1475379
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse