HELP TO CALL TWO stored procedures from another stored procedure

[biandinfosec]

I am new to writing stored procedure. I have created 2 stored procedure which seems to run ok. Now I have the need to invoke either of these two based on another condition in the db table. I want to be able to do something like the below narative:

created sp's:
sp_1 and sp_2.

condition_1 = true, then call sp_1
condition_2 = true, then call sp_2

conditions_1 and _2 are both true , then call sp_1 and sp_2

I am using Adaptive server anywhere v6.4

Can someone give me an idea how to achieve this? I want to combine the 2 sp's into one sp that crystal report can call. crystal does not support calling more than 1 sp in a report.

Any help will be appreciated. Thanks.

 

[smcnulty2000]

You should post your crystal version. It might help.

What kind of a conditions are going to drive this?

Scott.

 

[biandinfosec]

Crystal version: version 8.00

Conditions;
currently have 3 very complex reports - CR1, CR2, CR3. New requirement is to merge into 1.

For the sake of clarification. CR1 sql is used to create the sp1 and cr2 sql is used in creating sp2. no need to discuss cr3 at this point.

condition_1 is an indicator that is set in one of the table of sp1, with a 0 or 1 status, likewise, condition_2 is an indicator that is set in one of the tables of sp2, with a 0 or 1 status

condition_1 = 1= true, then call sp_1
condition_2 = 1= true, then call sp_2

this is the scenario. hope this helps.

 

[smcnulty2000]

Have you considered a UNION query that combines the results of SP1 and SP2? You can do this and have an extra field that has a single character in it indicating which sp it came from.

Then you just filter based on that extra field.

If the fields aren't going to be the same, which would be a requirement with a union query, you could fill out any missing fields with dead data.

Union queries look like this:

SELECT field1, field2, field3, "1" as Origin
FROM table
UNION
SELECT field1, field2, field3, "2" as Origin
FROM table

This is a database thing, not a crystal thing. As you can see the number of fields is the same, the data types should be the same, and the last field tells you which table it came from.

I have no idea if Adaptive Server supports Union queries, but they are likely to.

Good luck.

Scott.

 

[lbass]

Just a comment. In CR 8.0 to do a union statement, you would have to use the existing query for table 1, since the Select statement cannot be modified. After placing the fields you want on the report, you would go to "Show SQL Query", add "Union All" at the end of it and then add the same query for the second table, changing the table name. To create dummy fields in the initial query, you could add an otherwise unused field of the same datatype as the field in your second query.

-LB

 

[biandinfosec]

I am also posting this to a new thread, and thought should also put it here since this is where the original problem came from.

The link to the new thread is below:

http://www.tek-tips.com/viewthread.cfm?qid=1115728&page=1

Thanks for all your input.

-b