Nested Stored procedure calls

Question

Hi,

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
exec STORE_VARS
exec SORT_VARS
fetch next record
Loop
end procedure MAIN

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

procedure IDENTIFY_SAMPLE
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.

Answer 1

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
AS
CREATE TABLE #tmp1
(ID int)

EXEC dbo.sp2
GO

CREATE PROC dbo.sp2
AS
EXEC dbo.sp3
SELECT * FROM #tmp1
GO

CREATE PROC dbo.sp3
AS
INSERT INTO #tmp1 VALUES(1)
GO

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.

Nested Stored procedure calls

Follow this question

Related Questions