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
March 11, 2009 at 5:53 pm
lee.pollack (3/10/2009)
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.
You're lucky.
Look an learn how to build databases.
If you need SCOPE_IDENTITY then you lack of logic in you database and understanding of your data.
Relying on last inserted row identity limits you to single-row data processing. Ask Jeff Moden about RBAR, he has a lot to say about it. 😉
If you cannot tell from the data which row(s) you have just inserted then you have not a table but a dump containing not data but trash.
P.S. After years and years spent on database development, after countless data conversions and file uploads I should say I never needed to use IDENTITY related functions in any of my projects. Not a single time.
_____________
Code for TallyGenerator
March 12, 2009 at 5:52 am
you are prettey arrogant and well, not a very nice person. No need to be so condescending because one is not as experience as you are.:angry:
March 12, 2009 at 6:02 am
Sorry if it sounded as a personal attack.
It was not one.
There's nothing wrong in being unexperienced in database development.
I just tried to make a point that something what you're ready to consider (based on your tiny experience) as a stupid approach is in fact a great example of proper design.
And you should learn from it, not from other pretty common crappy examples written by VB programmers "with SQL skills".
If you recon it's arrogant - well, go ahead. I cannot stop you from destroyng good database.
_____________
Code for TallyGenerator
March 12, 2009 at 6:10 am
you are offensive - maybe because you lack the skills in the use of the english language. YOur use of "tiny experience" and the word stupid is offensive.
I have been working with databases since 1980 in many forms - mostly, dbase technology, so I do know how to manage databases. SQL is new to me - and the one thing I have learned is to be certain of what I am trying to do. Looking for other opinions and then making my own decisions has worked well for me for many years.
March 12, 2009 at 6:54 am
lee.pollack (3/12/2009)
you are offensive - maybe because you lack the skills in the use of the english language. YOur use of "tiny experience" and the word stupid is offensive.
My English skills are good enough to understand from yuor post that you considered "unique keys and triggers" as a stupid approach.
I never said it makes you stupid. I understood that it's just because your SQL Server skills are much less than my English skills.
That's why I tried to share my experience with you, and gave you a hint where you may look for good design samples.
You ignored good advice and decided to be offended.
Don't worry, I'm not gonna offend Your Brilliance with ideas you just cannot get.
Get advices from someone on your level.
_____________
Code for TallyGenerator
March 12, 2009 at 7:03 am
2 Items:
1: You mispelled your
2: I never said or implied that triggers were stupid. What I said was that the builders of the program that created the database and all it's tables used triggers and indexes rather than identity keys - that is all I said about triggers. When you want to provide assistance, you might want to be factual:)
March 12, 2009 at 7:14 am
Take it easy guys.... Take it easy.
There is nothing wrong with using Identity column in your tables. It all depends on the situation and what your needs are.
-Roy
March 12, 2009 at 7:17 am
Understood. We are taking this too far. I really appreciate all the assistance I am given. 😛
March 12, 2009 at 10:51 am
Lee,
Few comments and warnings on what might bite you in the future.
1. Calling SPs from another SP is not a big deal and has nothing to do with set based or RBAR programming. I use it quite extensively because it allows me to keep certain logic contained in a single unit and helps with testing.
2. Using identity is another thing that has nothing to do with set based programming vs RBAR. It has several adventages but I guess some people do not like it because it is not 'pure SQL' enough.... If it works for you - use it.
3. There is an issue realted to passing the data between ths SPs. Remember whenever you use OUTPUT parameter to retrieve a piece of information from the called SP that SQL treats that parameter as both input and output so if you are not carefull you might pass someting into your SP you expected not to be there.
4. SQL does not brake the execution of the next statement if the previous reported an error (unless you hit something sever considers fatal). So you have to evaluate for errors on each call to avoid chugging along although your code went belly up in the middle of it.
5. Using nested SPs makes managing transactions bit harder. SQL does not nest transactions. The last ROLLBACK undoes all the work. You may want to consider having the client manage the transactions instead of dealing in inside the SQL code.
6. Take each advice you get on the forums with a grain of salt (test the c.. out of it) - they are just advices not gospel. Remember you are the only one who really knows your particular business case... What works for others might not work for you and vice versa.
Good luck and have fun.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 11:01 am
JacekO, one error. SQL Server can nest transactions, to an extent. That's what @@Trancount is for.
Try this to see what I mean:
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Col1 char(1));
select @@Trancount;
begin transaction;
insert into #T (Col1)
select 'A';
begin transaction;
insert into #T (Col1)
select 'B';
select * from #T;
select @@Trancount;
commit;
select * from #T;
select @@Trancount;
rollback;
select * from #T;
select @@Trancount;
The nested transaction is committed, but that just reduces @@Trancount by 1, it doesn't actually commit anything. The final rollback gets rid of both rows.
Calling procs inside other procs does something very similar to that. It won't commit any of it till it commits all of it, or it will roll all of it back, or it will start causing problems by leaving an open transaction on the whole thing.
- 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 12, 2009 at 11:06 am
GSquared, with all due respect - SQL pretends to nest transactions :). If the last ROLLBACK uncommits all your pervious work this is not nesting in my book.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 11:09 am
Great Advice all the way thru - thanks. Since it is so good I will answer each one:
1. I absolutly agree with this logic - that is why I wanted to use them.
2. Identity keys can be very useful for record numbers and keys to tie back to other tables in relations and lookup situations.
3. Understood - will be careful
4. I found out about Fatal errors already when I actually tried to capture and return the error in a handler - handler code never executed - Fatal error simply stopped execution and returned to the calling program which in my case for this is VFP9. Lesson learned.
5. In this case, ROLLBACK undoing all the work is just what I wanted. Things had to be done in a specific order, but if one failed down the line the whole thing had to be undone. I like the idea that I can Begin Trans and then call all the SP and if need be ROLLBACK at the end by letting the SP return a values letting me know if I need to ROLLBACK - perfect.
6. Agree completely
March 12, 2009 at 11:12 am
JacekO (3/12/2009)
GSquared, with all due respect - SQL pretends to nest transactions :). If the last ROLLBACK uncommits all your pervious work this is not nesting in my book.
No. That's exactly what nesting means.
If you want partial commits (usually a really, really bad idea), don't nest the transactions, do them in sequence:
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Col1 char(1));
begin transaction;
insert into #T (Col1)
select 'A';
commit;
begin transaction;
insert into #T (Col1)
select 'B';
rollback;
select * from #T;
In that case, you'll still have A in the table, because that was committed, and you won't have B, because that was rolled back. Sequential transactions commit/rollback independently. Nested ones commit/rollback aggregately. That's how it's supposed to work, and it does.
- 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 12, 2009 at 11:20 am
thanks:D
Viewing 15 posts - 31 through 45 (of 99 total)
You must be logged in to reply to this topic. Login to reply