SAP Knowledge Base Article - Public

1208799 - Can stored procedures return multiple result sets in Crystal Reports?

Symptom

  • Stored Procedure returns different result set depending on the parameter value entered.
  • When reporting off a stored procedure that returns multiple result set, it generates error in Crystal Reports, or inconsistant results.
  • In Crystal Reports, is it possible to report off a stored procedure that returns multiple result sets?

Environment

  • Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In the database of your choice, create a stored procedure that returns a different result set based on the parameter value entered.
    For example, create a stored procedure that returns 4 database fields if one condition is met, or 2 database fields when it is not: 
     
    Create Procedure MyProcedure(@MyParameter nvarchar(30))
         Begin
             If @MyParameter = 'CAR' Then
                  Select Brand, Color, Model, Cost
                  From MyTable   
             Else
                  Select MyProduct, MyCost
                  From MyTable                
         End;
       
  2. Create a report in Crystal Reports off the stored procedure.
     
    It only return one result set, or return error messages.

Cause

  • Crystal Reports does not support stored procedures that returns multiple result sets.
       
  • Crystal Reports requires one static result set in order to add fields to a report, therefore if the stored procedure has condition, each of the output of the SELECT clause should always return the same number of database fields with the same name and data type.

Resolution

  • Modify or create a new stored procedure that only return one result set, therefore if there is conditions in the stored procedure based on the parameter values entered, ensure that all the SELECT clauses returns the same number of database fields, with the exact same name and data type.
     
    For example: If we have the following stored procedure:
      
    Create Procedure MyProcedure(@MyParameter nvarchar(30))
         Begin
             If @MyParameter = 'CAR' Then
                  SELECT Brand, Color, Model, Cost
                  FROM MyTable   
             Else
                  SELECT MyProduct, MyCost
                  FROM MyTable                
         End;   

    The stored procedure returns the following 4 database fields if the condition is met:
    - Brand  char(30)
    - Color   char(10) 
    - Model  char(30)
    - Cost    int
     
    But if the condition based on the parameter value entered is not met, then it returns the following 2 database fields:
    - MyProduct  char(30)
    - Cost         int

    It is not possible for Crystal Reports to manages multiple result set, since the report design required to know the database fields in advance to be able to design the report. Since the output is dynamic, then it will fail when a different data set is returned.  In order to keep the stored procedure conditions, ensure it always returns the same data structure for each SELECT clause.
         
    In this example, ensure it always returns 4 database fields with the same name and data type, therefore, modify the stored procedure to be like:
       
    Create Procedure MyProcedure(@MyParameter nvarchar(30))
         Begin
             If @MyParameter = 'CAR' Then
                  SELECT Brand, Color, Model, Cost
                  FROM MyTable   
             Else
                  SELECT MyProduct As Brand, 'N/A' as Color, 'N/A' as Model, Cost
                  FROM MyTable                
         End;  
       
        
    Note: The above is a generic example, and the syntax used for your database will be different. If you need information on how to create a stored procedure in your database, consult your Database Administartor, or your specifc database documentation.

Keywords

STORED PROCEDURE MULTIPLE RESULT SETS Seagate Crystal Reports Stored Procedure Support Seagate Crystal Query Stored Procedure Parameters , c2001503 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016