March 19, 2009 at 8:43 am
JacekO (3/19/2009)
The SQL Server transactions can be called nested transactions but they are not. Plain and simple. If they were nested they should work the way I described. There might be reasons why they can not be trully nested but this is not the point.So there is no reference or a book to be looked at.
So you're saying you came up with your definition of "nested transactions" out of the blue, with no input from anyone or anything?
If there is no supporting documentation for your definition of nested transactions, then I'm going to have to reject your definition and fall back on the way I was taught. You're arguing a semantic point that can't be proven, therefore, the majority rules on this definition.
However, if you can provide more information on where you came up with your definition, I would really be happy to look at it and make up my own mind outside of all the arguments previously posted on this thread.
Thanks,
March 19, 2009 at 8:48 am
Lee,
Overall your code looks fine (assuming I filtered out all your gutting properly)
Few comments:
1. Set BEGIN TRANSACTION as close to the statements that do INSERTS or UPDATES as possible.
2. When you have GOTO like in the below example you do not need the ELSE section . It adds additional lines of code and clutters the SP. The bolded code could be removed. I also do not like to use the GOTO. If overused the logic gets hard to follow.
IF @@ERROR <> 0
BEGIN
Select @errorNumber = @@ERROR
SET @errorMessage = @errorMessage + 'Warning: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' to TABLE 5 Table Failed' + CHAR(13)+CHAR(10)
GOTO ErrorHandler
END
ELSE
BEGIN
SET @blnInsertedB = 1
SET @blnExistsBRecord = 1
GOTO Finis
END
3. I think you mentioned a need of nesting stored procedures but I do not see you doing it here.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 19, 2009 at 8:48 am
lee.pollack (3/19/2009)
please remember that my company is still using sql2000 - so WHILE does not exist for me
Lee,
You can also nest IF...ELSE statements and CASE statements. It could be anything. I was just using WHILE as an example.
So, going by IF...ELSE, the psuedocode would be:
IF Condition1 = True -- outside IF
Begin
If Condition2 = True -- inside IF
Begin
--some code here
End
Else --inner IF...ELSE
Begin
IF Condition3 = true --Third level nested
.... --assumming 3rd leve statement is written out correctly
End --end second level nested
End --end first level nested (outer)
As far as your "assume the variables are correct" comment goes, unfortunately, if you want us to truly help you with that stored procedure, you're going to have to clean it up a little more. Otherwise other posters will get confused too and try to help you with the wrong problem.
If you want to risk getting a lot of incorrect replies, then leave it as it is, though.
March 19, 2009 at 8:50 am
lee.pollack (3/19/2009)
please remember that my company is still using sql2000 - so WHILE does not exist for me
"While" works in SQL 2000. Used to use it all the time in an SQL 2000 database, before I figured out better ways to get the same stuff done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2009 at 8:54 am
Brandie,
At one post I stated that SQL does not nest transactions and I proved my point. I have nothing else to say on this matter. I know and understand how the SQL Server 'nested transactions work'. There is a differnece between the term 'nested transactions' used in SQL Server and nesting transactions. I am sorry you do not see it.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 19, 2009 at 9:00 am
You're absolutely right, JacekO. There's nothing left to be said on the subject except, from what I read, you are the only person you proved your point to. I didn't see a single reply from anyone that indicated you'd actually convinced them.
That was the reason I asked for your sources. So I could prove or disprove the point for myself.
But, enough with dead horses. I've got a glue factory to run. @=)
March 19, 2009 at 9:12 am
I used the GOTO's to separate the code and make it more readable. I absolutely hate not using indentation - that makes code very hard to read no matter how good one is - just an opinion.
Original code had to be rearranged and the inserts and tests had to follow a specific order, something that was unknown at the time. That is why I want separate SP so I could move things around at will
March 19, 2009 at 9:27 am
You can use dedicated SPs to do just the inserts or updates and call them conditionally based on the logic in the main SP. That will make your code more readable and easied to test and debug if needed. As far as the indentation goes I love a lot of white spaces in the code so it does not look like spagetti.
I also use a lot of comments to make code more readable
For example I always try to label the BEGINs and ENDs to make sure I got the logic in the right place especially if they are nested (here we go again - the forbiden and misunderstood word...)
BEGIN ---MailChecks
BEGIN ---BuyMilk
BEGIN ---RakeTheGarden
BEGIN ---PaintTheHouse
END ---PaintTheHouse
END ---RakeTheGarden
END ---BuyMilk
END ---MailChecks
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 29, 2009 at 4:24 pm
Brandie Tarvin (3/19/2009)
JacekO (3/19/2009)
From my point of view they are not nested but maybe I could call them chained. To explain how I understand the term nested I provided an example in a pseudocode.JacekO, I don't want to just look at your pseudocode. I want to know where you got these definitions and who taught them to you.
Please give me references, book titles, names, websites and such so I can go back and see why you believe the way you do.
I'm not saying you're wrong and GSquared is right. I'm saying, I want to know the source of your understanding. Every SQL person I've ever met defines nested transactions the way GSquared does. I want to see where your information came from.
Please provide more information so I can research. Thanks, @=)
'Though on the surface it appears otherwise, SQL Server doesn't support truly nested transactions.' -Ken Henderson. The Guru's Guide to SQL Server Architecture and Internals. Page 514, last paragraph on the page.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 31, 2009 at 5:58 am
ABSOLUTELY
Viewing 10 posts - 91 through 99 (of 99 total)
You must be logged in to reply to this topic. Login to reply