Dynamic SQL Based on Parameter selection

[CrystalProgDev]

I need to generate Dynamic SQL Based on Parameter selection. Here is the scenario.

Parameters: Department ---- Values: A-Department, B-Department, C-Department

If user selects A-Department the SQL Should use Dept_A, Product_A and Sales_A Tables
If user selects B-Department the SQL Should use Dept_B, Product_B and Sales_B Tables
If user selects C-Department the SQL Should use Dept_C, Product_C and Sales_C Tables

We can do it by creating a stored procedure and call that stored procedure from Crystal. But Team is not agree with my advice. Is there any other way we can implement this in Crystal report? Please suggest. It is urgent.

Thank you in advance

 

[PeterDimitrov]

Why the team is against the stored procedure? Can you use a command.

Seems like your database is not normalized. Best practice in this case is to normalize it first and then filter the normalized data.

SELECT col1,col2...
FROM (
select 'Dept_A' as Department, col1,col2 ... FROM Dept_A, Product_A, Sales_A ...
UNION
select 'Dept_B' as Department, col1,col2 ... FROM Dept_B, Product_B, Sales_B ...
UNION
select 'Dept_C' as Department, col1,col2 ... FROM Dept_C, Product_C, Sales_C ...
) t
WHERE t.Department = {?Department} and .... the other parameters here

This might be slower compared to an "if" clause , which is checking the Department value and running a separate query for each case

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[PeterDimitrov]

The approach above will allow you to use a view

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[CrystalProgDev]

Thank Peter