Skip to Content
0

Using a single Stored Procedure for multiple Crystal Reports

May 16, 2017 at 08:47 PM

77

avatar image
Former Member

I know how to pass parameters from Crystal to my Stored Procedure, but I have a special application I did years ago that I don't remember how to do now.

I'd like to create a SP with many different queries. Each query will be used for a different crystal report, and will have the prefix/suffix to select the correct query as follows:

if @rptname='thevaluefromcrystalreport' then

begin

Select query is inserted here

end

The next query in the SP would start "else if".

I clearly remember the SQL syntax. The issue is that I cannot remember how to "store" the report name to be passed. The user should not have to input this as a parameter. The parameter value @rptname should be save with each the crystal report to be passed to the SP when the report is run. Each report will be created with its unique value to be passed to the SP so that only the appropriate select statement will be called for each report from the same SP. The value should be stored separately from any parameters that the user may be asked for (e.g. date range).

As I said, I figured this out about 8 years ago, but don't remember how I did it.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ian Waterman May 17, 2017 at 08:43 AM
0

You should be able to default the parameter value/string to the required name for each report.

Ian

Share
10 |10000 characters needed characters left characters exceeded
Luis Darui
May 17, 2017 at 06:15 PM
0

You can logically chain if... else if... else if... until you meet all your conditions.

Example 1 (without else...if...)

CREATE PROCEDURE [dbo].[MyTestProcedure]
@parameter [int]
AS
BEGIN
	
	IF (@parameter = 1)
		BEGIN
		--ADD YOUR CODE HERE
		RETURN 1;
		END;
	IF (@parameter = 2)
		BEGIN
		--ADD YOUR CODE HERE
		RETURN 2;
		END;
	IF (@parameter = 3)
		BEGIN
		--ADD YOUR CODE HERE
		RETURN 3;
		END;
	RETURN NULL;
END;

Example 2 (with else if):

CREATE PROCEDURE [dbo].[MyTestProcedure]
@parameter [int]
AS
BEGIN
	IF (@parameter = 1)
		BEGIN
		--ADD YOUR CODE HERE
		RETURN 1;
		END
	ELSE IF (@parameter = 2)
		BEGIN
		--ADD YOUR CODE HERE
		RETURN 2;
		END
	ELSE IF (@parameter = 3)
		BEGIN
		--ADD YOUR CODE HERE
		RETURN 3;
		END;
	RETURN NULL;
END;

My recommendation?

Don't do this. Write a procedure for each need. Don't make your code big and hard for maintenance.

If you change a single report and needs to update the procedure, well, you will put into risk all other reports.

Share
10 |10000 characters needed characters left characters exceeded