Nesting transactions/SPs, output parameters, other questions & general chaos :)
-
Ok... kinda a follow up to this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=192612
I took a break from all this for week or so, but I've been beating my head against some things for the past 2 days. But it's the end of the day on a Friday here and I'm too fried to clean up any code to post right now, so I'll stick to "theory" questions for now.
Background:
As mentioned in the above thread... (Read for some code & more details)
What I need to do is insert a patient, a patient visit record and then the related form data for that "incident". As the patient and patient visit record inserts will be reused lots, I pulled them out and have them in their own SPs. And for ease of coding, readability, etc, I pulled the form data insert out into it's own SP as well.
So, there is an "outer" SP that will call (and manage) these 3 SPs mentioned above.
Obviously each insert will be dependant on the prior one's success and will require the newID for the next insert. I.e. New Patient is inserted, PatientID has to be returned for the patient visit record insert (as it is a FK) and so on.... And, of course, if anything fails in the insert into these 3 tables, all must roll back and no one/no thing is inserted as it's a "group effort".
Questions:
I am struggling with the best way to handle this situation. I.e.:
1) Should I be using SPs for the inner calls? I have been trying to do it using return values, or an output parameter to get the ID to carry back to the calling outer SP to then pass as a variable to the "inner" SP2 for it's FKid. But... it is not working and I'm ready to hear anything and everything on what might be a better way to handle things.
2) So would converting these inner SPs into FUNCTION(s) be a better solution and if so, how can I consistently capture that return value (newID/@@IDENTITY) from the last insert to pass on for the next insert?
3) OR would calling a TRIGGER be better... New patient insert triggers New patient visit record insert which triggers form data table insert??!!
Help!
Issues:
Lots... but I can expand more based on questions/responses!
Thanks in advance! May not be able to check back until Sun MST sometime, but will post code if needed too.
Cheers,
Michelle/Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
This was removed by the editor as SPAM
-
Hey there,
Just following up on my post from last Friday afternoon... no one has responded as it likely dropped off the board before anyone saw it.
I could really use some help/advice here.. hopefully someone out there can respond.
Thanks,
Cheers!
Michelle/Dolphin
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
Your methodology of having 1 insert procedure for each table is good, Promotes reuse. and creating a wrapper to take all the input, and insert to 3 tables is fine.
Your return values are not working because you have to declare an output parameter to the stored procedure
For example
Create procedures InsertPatients (@PatientFName varchar(50), @PatientLName varchar(50), @PatientID int OUTPUT)
as
Begin
-- Your insert code here
Insert into Patients(PatientFName, PatientLName)
Values (@PatientFName, @PatientLName)
set @PatientID = scope_Identity()
End
then within your Wrapper,
Declare @PatientID int
Exec InsertPatients @PatientFName = 'Joe', @PatientLName='Patient', @PatientID = @PatientID output
-- Then you can use @PatientID later in your wrapper as parameter to other stored procedures.
-
-
Noel,
Thanks... but I've had this article recommended more times than I can count, and I've read it at least that many times as well, if not more
Unfortunately, it does not address my specific questions/problem here, and it does not address situations with nested SPs with any degree of complexity.
Actually that tends to be my "pet peeve" with most articles, on-line examples, even most books... is that they don't take the examples to any actual "real-world" level of complexity. If everyone only ever inserted/updated just 1 table at a time, or didn't have to call more than one SP directly at a time, or.... (so on), it would be a perfect world. And along with those examples... a real explanation of what each line of code does, why use (why not to use) is rarely provided.
If only the world was just "copy & paste" (like I was "taught" in school), these articles/books/resources would be enough... but it isn't. Sorry... my sarcasm is shining through, but as someone who is trying to actually learn/understand the real theory/reasoning behind doing things a certain way, it is very frustrating to get a one line response with a link to an article that is probably posted ~100 times on this forum alone.
So... sorry for the rant... apparently I need to start each post the list of things I've tried already to figure it out so people's time (including my own) isn't wasted
Thanks again for trying though...
Cheers.
Michelle/Dolphin.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
To be honest ALL your questions are anwsered in that article but if you need my views here we go:
I am struggling with the best way to handle this situation. I.e.:
1) Should I be using SPs for the inner calls? I have been trying to do it using return values, or an output parameter to get the ID to carry back to the calling outer SP to then pass as a variable to the "inner" SP2 for it's FKid. But... it is not working and I'm ready to hear anything and everything on what might be a better way to handle things.
Did you read the post previous to mine ?
Just use the word OUTPUT after the output paramenter of the stored procedure call that returns the FK value
Exec InsertPatients @PatientFName = 'Joe', @PatientLName='Patient', @PatientID = @PatientID output
2) So would converting these inner SPs into FUNCTION(s) be a better solution and if so, how can I consistently capture that return value (newID/@@IDENTITY) from the last insert to pass on for the next insert?
I don't think so! are you trying to insert multiple patients at a time ?
3) OR would calling a TRIGGER be better... New patient insert triggers New patient visit record insert which triggers form data table insert??!!
this last poit may be valid if you are trying to insert multiple patient data at a time which I doubt is ussually the case
About NESTING transactions
I will only use one transaction and never nest them because the presense of triggers can make things go bad in case of a rollbak.
In pseudo code:
1.Just start your transaction at the begining of you wrapper (caller sp)
2.call the three procedures checking for success or failure
3.in case all succeded commit otherwise rollbak the wrapper transaction
hth
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply