Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temporary Stored Procedures


Temporary Stored Procedures

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Roland Howard Boorman
Roland Howard Boorman
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
Mr.DiGi (6/27/2013)
Well, this is dafuq of day... w00t


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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45038 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
theboyholty
theboyholty
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 123
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.
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4450
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
curt_coker
curt_coker
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 86
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).


Well, it has been a couple of years since this topic appeared. Since then Kendra Little posted this nice little video regarding the performance of a stored procedure vs. a query parameterized with local variables: http://www.brentozar.com/archive/2015/03/local-variable-vs-stored-procedure/

The conclusion is that if you want to see a "real" query plan using production database statistics without deploying the procedure to production then a temp procedure is the only way to go. You can't use a query parameterized with variables. This gives further weight to Solomon's point made waaay back in 2013.

Curt
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search