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

• 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?
     
      
• Note: Example provided in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

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.

• 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.

• 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.

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 Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020