Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2004-09-16 : 11:56:53
|
Greetings all,First time back in a long time (Wedding planning and New Jobs do that to you, it seems  ), so I have what I hope will be an interesting topic that I can raise, and smart people like you can point of which light is the oncoming train, and which one is the way out of the tunnel  Basically, what I specifically am trying to look at is best ways to allow pre-conditino and post-condition checking inside my stored procedure, and to have the ability to turn the cheecking on and off.(1)So, one way would be to have a bit type parameter, default 0, which is disabled, and pass through a 1 whenever we want to do pre-condition and post-condition checking. Not a way I want to go, since I have to change the calling code (or have the calling code pick up what to do off of a config file or some similar area), and having additional parameters.(2)Create a 1 row tables that has an entry containing the "state", and having all SP's check the table to determine whether they should pre-check and post-check. Not particularly keen on this - introducing an extra SQL call in ever SP.(3)Trying to find some way of defining a global setting that can be referenced, say via a UDF, to determine whether we should be pre-checing and post-checking.I am sure that some of your initial reactions will be around the performance implications of doing unnecessary work, but the counter for that is creating code that is robust and can do strong error handling - an interesting debate as well, I am sure.Thoughts, comments, general laughter?CiaO*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-16 : 13:33:06
|
| I have done similar like this:exec ('create table ##temp_'+ convert(varchar,@@SPID) + '(col1 int))')Then in my procs and triggers where I want to conditionally perform operations I haveIF object_id('tempdb..##t_' + convert(varchar,@@SPID)) Is Null process here...Then a process can enable or disable processing for itself. The table will go away when the process disconnects to it does not have to worry about cleaning up after itself.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-09-17 : 03:43:15
|
| Thanks Ken,Reading your, if I catch your drift, you will have checking on some SPs/Function and not on others... that checking is always on, unless you change the code to remove the checking?I am trying to find a solution which I can create external to modifying code, since the code the I use and develop in DEV, should be the same code I roll into TEST, and since it is obviously perfect (yeah right ), the same code the goes into our PreProd environment, and finally production. I am looking at having a way whereby I can turn on or off the checking at a environment level. I could do this with a presistent table, but as I said before, that would reguire an extra select in every stored proc - was trying to find a better way.Thanks for your response!*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-17 : 12:23:09
|
| I see. My suggestion could still be used in that environment but midified slightly.Rather than selecting a row from a permanent table, just create the permanent table in dev.creat table dev_nochecks (col1 int)Then in your procs you could addif object_id('dev_nochecks') is not null... process hereIt is similar to reading a row from a table but faster with less overhead than actually reading the table. Just check to see if it exists instead. Do not create the table in the production system.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 06:35:34
|
| Do all your SQL calls go through a function (e..g to actually do the EXEC) in the client application? If so could you just twiddle a parameter to the SProc if appropriate (which would otherwise default to some value in the SProc parameter definition.Kristen |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-09-20 : 06:44:19
|
| Thanks Kristen, Ken.Pretty much variations of my thinking. AFAIK, there are no user defined DBoptions - at least I haven't been able to find them. I guess the difference between have a function call to check an option, or doing "if object_id('dev_nochecks') is not null" is insignificant. I think that the checking of a parameter (bit 0 / 1) would probably be faster.I must check to see how theb developer's "turn on" the parameter checking for the .Net code - perhaps I can reference the same switch somehow...thanks for the comments, guys*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
Design by contract ... applying principles to SP's