SQL stored procedures - calling one from another

[tyme]

I have a problem:

 

I have a stored procedure that will either execute a query, or pass operations on to another stored procedure. i didn't write the original code, but it appears as if the data returned when calling the second sproc never gets back through. The basic format is like:

If (conditions)
begin
....some stuff
end
else
begin
	exec @ReportDataSource @Range, @StartDate, @EndDate
end

 

@ReportDataSource being the name of the sproc I want to call, @Range being hour/day/month, and the last two are pretty obvious. I have a feeling I need somevariable OUTPUT to receive data from the sproc I'm calling, but the problem is that depending on the sproc I'm calling I could have two or more results coming back...does it all get returned as one? or do I have to account for all of them? And will the OUTPUT method even work?

[Gowator]

its been a while...but ...

What database is it?

Is it PSQL?

 

usually the stored procedure will itself hold the definition of the data source, if its a report for instance then usually you just pass the limits of that report...

 

like I say, its been a while but i used to program Oracle 6 for a job..

[tyme]

MS SQL, unfortunately. Not my choice...job stuff. i know the sproc it calls returns the information...i need to test the original sproc outside of the web environment, though.

[Gowator]

MS SQL, unfortunately. Not my choice...job stuff. i know the sproc it calls returns the information...i need to test the original sproc outside of the web environment, though.

I can tell you how it works in Oracle and perhaps that will help by example? As you probably guessed I have zero MS SQL experience...

 

Within Oracle you would define a report... this could be attached as a trigger or just a seperate standalone report defintion.

The definition would contain the structure information but not the limits... but it could hold the definition of the limits as a defined variable..this variable (or variables) would be passed from the PSQL to the report..

 

Its difficult to go further because of the degree of abstraction here (sorry) ..

wiothin Oracle the difference in

PL/SQL between a function and a procedure is that functions return a value, procedures don't.

 

So I think overall you might need to start looking at this problem in reverse from the definitions and declarations upwards not from the procedure downwards???

 

Sorry, I realise that's not a great help but its obviously a pretty interlinked procedure