Crystal Report using 2 store procedures or 1 stored procedure to exec stored procs based on a parameter

I have to make a report using crystal reports 2008.  The report has a parameter  in which the end user can choose between 2 options - Admitting or Attending.  

Base on what is chosen a stored procedure will execute.
I was trying to make one stored proc, that would execute one of 2 stored proc based on a parameter but the problemsis both stored procs have paremeters.  
See my SP below it doesnt work.  
The last 3 parameters are the ones that are in the stored procedure

ALTER PROCEDURE [Customer].[bhsf_rpt_RunPhyRelSP_FO]
 @PatRel varchar(20),
 @ProvCode  VARCHAR(10),
 @StartDate  Date ,
@EndDate Date
 as
  Begin
  SET NOCOUNT ON;

If @PatRel = 'Admitting '
      Execute Customer.bhsf_rpt_AdmittingOnly_FO
Else Execute Customer.bhsf_rpt_Attending_FO

 End


 So basically what I am asking is how can I make a stored procudure execute other stored procedures that have paremeters or how can I add to stored procedures to run in crystal reports and have 1 run based on the paremeter that is chosen?
Butterfly2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Will the report have a subreport?

Do both SPs use the same parameters?

I believe when you call another SP you have to pass the parameters.

mlmcc
0
Butterfly2Author Commented:
Will the report have a subreport?  I am trying to avoid it, if possible

Do both SPs use the same parameters? Yes

I am not sure how to pass the parameters.
0
LinInDenverCommented:
What is the reason you want to avoid sub reports? I think that would be a good solution for you.

Have a main report with a parameter. If it wants you to have a data connection, just make a command with a fake select. I.e.: on SQL Server, you could say select 'x' and on Oracle you can say select 'x' from dual .

The parameter determines which sub report/stored proc is called.
Create two detail sections on the main report and drop a sub report in each.
Conditionally suppress the sections based on parameter selection:
for the one for Admitting, if {?parameter} = 'Attending' then yes else no
for the one for Attending, if  {?parameter} = 'Admitting' then yes else no
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

UnifiedISCommented:
To execute the proc, add them after the execute command in order (based on the proc you are calling), separated by commas

Execute Customer.bhsf_rpt_AdmittingOnly_FO @PatRel, @ProvCode

Any parameters needed by your sub procs need will need to be passed to the main proc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Butterfly2Author Commented:
Let me try to do the sub reports and see what I come up with.
0
UnifiedISCommented:
I liked your stored procedure architecture and would definitely be my choice. Sub-reports are always a last resort for me.
0
Butterfly2Author Commented:
So I gave up on trying to do the sub reports, the format is not coming out correctly, and my page footer is only showing up on the last page.

So Now I am back to the stored procedure

So I tried putting the paremeters after the stored procedure

ALTER PROCEDURE [Customer].[bhsf_rpt_RunPhyRelSP_FO]
 @PatRel varchar(20),
  @ProvCode  VARCHAR(10),
  @StartDate Date,
  @EndDate Date
  as
  Begin
  SET NOCOUNT ON;

If @PatRel = 'Admitting '
      Execute Customer.bhsf_rpt_AdmittingOnly_FO @PatRel,@ProvCode , @StartDate, @EndDate
Else Execute Customer.bhsf_rpt_Attending_FO @PatRel,@ProvCode , @StartDate, @EndDate

         
End
 

It is saying that I have too many arguements specified
0
UnifiedISCommented:
I'd assume @PatRel is not a a parameter in your sub procs since that value is used to determine which sub proc to call.  
Only pass the parameters that the sub proc requires in your execute statement, maybe like this:
 Execute Customer.bhsf_rpt_AdmittingOnly_FO @ProvCode , @StartDate, @EndDate

bhsf_rpt_AdmittingOnly expects 3 paramaters (a code and 2 dates)?
0
LinInDenverCommented:
For sub reports to display page footers correctly, you can probably just put the page footers on the main report instead of trying to put them inside the sub reports.

For your other attempt, do you have the report pointing only to bhsf_rpt_RunPhyRelSP_FO? If so, it should prompt you for all 4 parameters automatically.

Do your other two stored procs really have all 4 variables (@PatRel,@ProvCode , @StartDate, @EndDate) or are there only 3 (maybe not having @PatRel)?
0
Butterfly2Author Commented:
UnifiedIS, I cought that after I posted it, once I removed that parameter it worked fine.  I was able to use it in my crystal report, which is working fine.

LinInDenver,

I really appreciate your input and assistance but I just couldn't get the sub report to work they way I wanted it.
0
mlmccCommented:
Subreports by definition do NOT have page footers.  There are tricks you can use to simulate them.  If an existing report is used for the subreport, the page header/footer becomes the subreport report header/footer.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.