I am new to SQL Server. I am in the process of migrating several Foxpro Programs into SQL Server stored procedures for the hospital I work for. I have one particular huge Foxpro program which refers to some lookup tables, performs many logic and validations and builds a single destination table. This destination table needs to be updated daily overnight to reflect any new patient details.I need to migrate it to SQL Server but am having difficulty as the original FOXPRO program makes calls to stored procs from within stored procs. The Foxpro program follows a logic something like below - I have made it simple just to convey the idea: My question is, is there a way to achieve it using sql server 2005?
***** each of these is a user created stored proc which inturn performs other logic and validation and then either updates or inserts a row in the destination table. Some of these even call other small procedures and finally the control returns back to the calling procedure (i.e. SORT and then back to MAIN).
I would greatly appreciate your valuable advice on how to go about achieving this i SQL Server 2005 as I found that the control does not return back to the intermediate calling procedures but returns to the MAIN procedure and hence the logic in between does not work.
I apologize for the cumbersome nature of this query but any help to clarify the way SQL Stored Procedures work and how to achieve row by row manipulation will be tremendously helpful to me.Many thanks in advance.
asked Jul 27 '12 at 10:14 AM in Default
It should be no problem to use nested stored procedures. The control should return to the calling procedure unless you get some kind of exception that can't be handled. If you need to pass data between the procedures you could use temporary tables (tables with # prefix). The code below give you a short sample of nested procedures:
BTW! You should NOT migrate to SQL server 2005! That version has passed mainstream support and you should not build new applications on that version unless you want to have trouble in migration later on.
answered Aug 01 '12 at 11:01 AM