Nested Stored procedure calls


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?

 procedure MAIN
 Do while Not EOF
 fetch next record
 end procedure MAIN

 procedure STORE_VARS
 'store value in each column into respective variables
 ''''control returns to MAIN with the stored values in variables
 end procedure STORE_VARS

 if columnA = '*something*'
 store '*aaa*' to varSample
 else store '*bbb*' to varSample
 end if 
 '''control returns to STORE_VARS with the value of varSample
 end procedure IDENTIFY_SAMPLE

 procedure SORT_VARS
 if varA = 'organismName'
 exec **ORGANISM***
 else if varA = 'sensitivity'
    exec **SENSITIVITY***
 else if varA = 'growthCode'
    exec **GROWTH***
 else if varA = 'organismComment'
    exec **ORGCOMMENT***
 End If
 end procedure SORT_VARS

* 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.

more ▼

asked Jul 27, 2012 at 10:14 AM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

 CREATE PROC dbo.sp1
 (ID int)
 EXEC dbo.sp2
 CREATE PROC dbo.sp2
 EXEC dbo.sp3
 SELECT * FROM #tmp1
 CREATE PROC dbo.sp3

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.

more ▼

answered Aug 01, 2012 at 11:01 AM

avatar image

Håkan Winther
16.4k 36 45 57

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 27, 2012 at 10:14 AM

Seen: 1820 times

Last Updated: Aug 01, 2012 at 11:01 AM

Copyright 2016 Redgate Software. Privacy Policy