March 10, 2009 at 7:54 am
Are you saying I need to start all this over again in another forum?
March 10, 2009 at 7:56 am
You are almost correct. It is not an all or nothing in all situations. The fatal errors will of course never get to the commit section anyway.
I only rollback if the is a self imposed fatal error - the rest are simply warning sent back to the user with inforamtion and instructions
March 10, 2009 at 7:59 am
I will look into the EXISTS you mention. I was usign the @@ERROR test to also decide what information I would add to the return mesage. There are several errors that are deemed as only warning to my system
March 10, 2009 at 8:30 am
I would not repost in a SQL Server 2000 forum at this time as people have already started helping you out here. However, I would just be sure that you be sure to put future posts in the SQL Server 2000 forums.
March 10, 2009 at 8:31 am
Moving to SQL 2000 - Development forum.
The IF Exists should help you develop good SQL flow. I'd avoid the GOTO, though honestly it's something of a habit. I don't know if it affects the way that SQL actually runs.
My idea for structure would be
-- insert header
-- if exists (select from header)
begin
add child
end
else
begin
set error var
return -- break out of proc
end
-- if exists (select from header or child)
begin
add next record
end
else
begin
set error var
return -- break out of proc
end
you could repeat this in a proc to get an orderly flow, and by setting the error variable, you can easily determine where the issues are.
March 10, 2009 at 8:33 am
Don't bother moving to another forum. No need to start over. If you like, you can edit the original post and just add something that says "Accidentally posted in SQL 2005 forum. Using SQL 2000.", or something to that effect. Helps when people start to answer.
What you're doing with calling stored procs from within other stored procs is a pretty standard thing. It can even help with execution plans in many cases. Very normal to do it.
I would personally avoid GOTO as much as possible, because it makes the proc harder to follow, but for error-handling in SQL 2000, it's about the best option you have, so go ahead and use it for that.
What you posted in your original post looks pretty standard to me. Nothing new or unusual there. Should work just fine.
One suggestion, though, is instead of just checking error codes, use Scope_Identity() or something of that sort to make sure that you actually inserted a row.
- 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 10, 2009 at 8:34 am
understood. BTW EXISTS works nicely so far
March 10, 2009 at 8:35 am
sorry - the psuedo code was an example of what I wanted to do, the actual code is all in one procedure
March 10, 2009 at 8:38 am
waht is scope_identity()
March 10, 2009 at 8:43 am
If you have identity column in your table, then you can use the function to return the ID of the row that was inserted. You can get details regarding it in Books On line.
-Roy
March 10, 2009 at 8:45 am
Just looked up scope_identity. Nice, except for one thing - no yelling now, I did not right the program, there tables DO NOT seem to have any identity fields [primary keys] they did it all with triggers and unique non-clustered indexes. So I dont think @@Identity or scope_identity() will help -
or am I mistaken
March 10, 2009 at 8:51 am
Nope... Identity wont work unless your table already has it. My guess is that you will have to live with what you have.
Maybe giving the table structures and some sample data would help giving you better advice.
-Roy
March 10, 2009 at 8:57 am
Your right - what works works. Appreciate all the help here and have learned a lot for future tasks - we can close this issue now
March 10, 2009 at 9:54 am
Lee,
you are welcome, and we're glad to help.
If you have more questions, start a new topic in the forum that seems to fit best.
March 11, 2009 at 11:18 am
About the use of the GOTO: this is not a religious issue.
Yes, GOTO can generally be avoided but there are exceptional cases where it is useful. For instance to terminate a recursive routine. The anathem on GOTO stems from the late 70's where engineers happily produced unreadable spaghetti code after being thought by an assistant teacher how he programmed things.
I also see nothing wrong with a stored procedure calling another stored procedure, though you will have issues (not insurmountable) passing data between the stored procedures.
If you want to do some branching, based on the results you could do something like this:
DECLARE @li_Results int
SET @li_Results = -1
EXEC @li_Results = YOUR_STORED_PROC_1
IF @li_Results = 1
EXEC @li_Results = YOUR_STORED_PROC_2
ELSE
EXEC @li_Results = YOUR_STORED_PROC_3
One use I have for the GOTO is to break out of a stored proc when a transaction might have been started or not.
DECLARE @lb_InTransaction bit, @li_RetCode int
SET @lb_InTransaction = 0
SET @li_RetCode = -1
SOME_CODE
IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0
IF @li_RetCode <> 0 GOTO QUIT
BEGIN TRAN
SET @lb_InTransaction = 1
SOME_OTHER_CODE
IF @@ERROR <> 0 SET @li_RetCode = -3
IF @li_RetCode <> 0 GOTO QUIT
SOME_OTHER_OTHER_CODE
IF @@ERROR <> 0 SET @li_RetCode = -4
IF @li_RetCode <> 0 GOTO QUIT
MAYBE SOMETHING_ELSE
IF @@ERROR <> 0 SET @li_RetCode = -4
QUIT:
IF @lb_InTransaction = 1 BEGIN
IF @li_RetCode = 0 COMMIT TRAN
ELSE ROLLBACK TRAN
END
RETURN @li_RetCode
Viewing 15 posts - 16 through 30 (of 100 total)
You must be logged in to reply to this topic. Login to reply