This is really a SQL Server 6.5 issue, but as I am developing the Client side using VFP, I thought I would try here.

I want to call a stored procedure, from a stored procedure, however, the second stored procedure call will be built from a string;

e.g. Select @SpCall = 'Execute ' + @SpName + ' ' + @param1 + "," + @param2, etc...

The problems that we are facing are:

1) If any variables which have been passed into procedure 1, are to be used in procedure 2, they need to be declared within the calling block.

e.g. execute (@spCall)

will fail with a @param1 and @param1 undefined (based upon the example above. This can be easily avoided by building the string explicitly using the datatype specified in procedure 1.

i.e.

Create Procedure FirstProc @param1 int, @param2 char(10) As
.
.
.
Select @spCall = 'Execute ' + @SpName + Trim(Convert( char(15), @param1 )) + ' int ' + ...
.
.
Execute (@spCall)

2) The more serious problem is that if Procedure 1 wants to test the return status of the execute string (@SpCall), there appears to be no way of doing it.

I hope that this makes some sense. I will be only to glad to supply further information on cgf@postmaster.co.uk

Thank you in advance

Clinton Forde