March 10, 2009 at 6:04 am
I am using a series IF ELSE and GOTO statements in a stored procediure.
I would like to break out the code in the GOTO to separate stored procedures.
However, I am not certain as to how call a stored procedue from withing a stored procedure and return back to the calling stored procedure. For Example
IF true
EXEC sp1
ELSE
EXEC sp2
EXEC sp3
EXEC sp4
IF false
EXEC sp5
ELSE
EXEC sp6
GOTO errhandler
errhandler:
handle the errors
GOTO finish
March 10, 2009 at 6:20 am
I have rarely seen stored procedures that call other stored procedures. Mostly they call functions.
Have you tested your Exec statements yet? Your pseudo-code looks basically correct.
March 10, 2009 at 6:53 am
Then I am to understand - when one of the stored procedures has completed it's task, it will return to the calling procedure.?
I usually try to follow the principle that a funciton returns a value while a procedue performs an action. ALl of the code in my GOTO are inserting records to table. While I do test for success, I felt that the inserting was the primary action.
March 10, 2009 at 7:12 am
Yes, calls to other SP's return to the calling one.... take it as if they were just subroutines called.. jeez... this takes me back 20+ years programming in BASIC 😀
March 10, 2009 at 7:15 am
Lee,
I think they do, but don't take our word for it. Stick in a dummy proc in your GOTO that creates some fake data, then stick another dummy proc in where you expect the return to go to. This is the only way to know for sure that your code is working the way you expect.
March 10, 2009 at 7:16 am
Been programming in Foxpro for many years, never heard of GOTO before. SQL is so picky that I just wanted to be certain before I blew something up that works. Thanks for the slam:crying:
March 10, 2009 at 7:18 am
You can call SPs within an SP but maybe you can analyze your requirements more and see if you can do everything in one stored Proc with SET Based Operations.
Please keep in mind not to call the SPs within in a loop. There is a limit to nested Stored proc calls.
-Roy
March 10, 2009 at 7:21 am
Slam? I didn't mean to slam. Sorry if you took offense at my reply.
Roy is correct about the procs, though. What you've got will work, but it'll be heck on your processing ability. Is there a reason why you need to have so many nested procs?
Why can't you use CTEs or temp tables to get your results?
And lastly, what errors are you looking for that you actually need the whole GOTO instead of a TRY CATCH?
Depending on what you're trying to do, an SSIS package or a .Net assembly might be better than nested procs.
March 10, 2009 at 7:24 am
Everything is already in one stored procedures. Not real familiar with the term SET BASED OPERATIONs but assume it means handling flow with mem vars.
The stored procedure has become huge in print (mostly becasue I like to make it readable so I list the fields and value rather than place them on a line.
Changeing the order of the GOTO is problematic since I am unsure if the order that the GOTO appear in the program means anything. VERY NEW TO GOTO
March 10, 2009 at 7:29 am
Can't use try catch - still in SQL 2000 :(.
Don't know what a CTE is - my bad.
I check to see if the record was inserted or not by testing @@ERROR <> 0 and then branch to the GOTO, either the error handler or to the next record to insert.
The procedure is designed to insert one item into several tables to make a valid code in the system.
March 10, 2009 at 7:34 am
lee.pollack (3/10/2009)
Everything is already in one stored procedures.
Lee, according to your pseudo-code in the OP, you're calling at least 4 different stored procedures. That's not really containing everything in one stored procedure.
Not real familiar with the term SET BASED OPERATIONs but assume it means handling flow with mem vars.
Actually, variables have nothing to do with Set Based Operations.
Here's an explanation I gave a co-worker on Set Based the other day:
A mass update (Set Based Operation) from a DBA perspective is like pushing a nail magnet across the floor and the magnet picks up all the nails with minimal work on our part. All we do is push the magnet (or a simple bit of code).
What you're asking us to do in this particular case, though, is to individually pick up only certain nails and to leave other nails alone. Unfortunately, because the nails don't have anything in common, we have to manually sort through all the nails to find the correct ones. Then you'd need to sort through the nails again to make sure we didn't miss anything or pick the wrong nails to begin with. (This is RBAR)
Here are some articles that will help you on RBAR / Set Based Operations:
http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
http://www.sqlservercentral.com/articles/Performance+Tuning/62278/
As far as GOTO, I don't ever use it. I've never needed to.
Again, what are you trying to accomplish with the procs? What's your data like (you can sanitize) and what is the end result supposed to be? And what errors are you trying to process / change?
March 10, 2009 at 7:35 am
I think you should have posted in SQL 2000 Forum and not sql 2005. When you post in SQL 2005 forum, the answers you get will be based on the functionality of SQL 2005. That is why the suggetion of Try Catch, CTE's came up.
Usually you use @@Error to commit transaction or Rollback transactions. We do not have enough info till now to give a good suggestion. Please provide more data about what exactly you want to do.
-Roy
March 10, 2009 at 7:47 am
by passing 2 parameters from Visual Foxpro [VFP9] procedure to SQL thru SQL passthru
I create one header record, one edit record, one setup record, one revenue code record and one billing info record in an accounting system that tracks projects.
Certain records MUST exist before others can be inserted. For example the header record must exist before the edit record can be inserted.
Fatal errors are untrappable since they immedately terminate the procedure, so they are captured inside VFP9. Other errors are passed back to VFP9 as well as results of successes.
The errors and/or successes are emailed to the appropriate individals with actions required if any.
I am using GOTO branching because it breaks up the code for readability and so I can branch. The IF ELSE programming in SQL is tough to get a good handle on sometimes. I am used to IF ELSE ENDIF and sql has not ENDIF. I am not used to having to use Begin End just to make the IF ELSE clause work. I am sure it will al get better, just a learning curve.
Doe this help
March 10, 2009 at 7:50 am
Lee,
I just caught up to the fact that you're using SQL 2000. You need to repost this in that forum. However, you can still use Temp Tables (look them up in Books Online) to create your records one at a time within the same stored procedure, then send them out all at once.
March 10, 2009 at 7:52 am
From your description this sounds more like statements within a TRANSACTION, either all commit or all fail..... There would be no need to call different SP's for that and if any statements fail, the whole thing is rolled back and you can know the end result by that as well....
Viewing 15 posts - 1 through 15 (of 99 total)
You must be logged in to reply to this topic. Login to reply