Stored Procedure Returns No Fields Even After Refresh Fields

Introduction

This tip is about using stored procedure in SSRS or Entity data model. If Stored Procedure uses dynamic SQL or returns data from temp table, then SSRS or EF won't be able to generate dataset or resultset respectively. Read below to solve this issue.

Background

I was working on an SSRS report with dynamic SQL procedure. I was familiar with FMTONLY issue so I altered my stored procedure in that way. But, I was not aware of handling null values. I am finally done with this.

Using the Code

While creating any SSRS report using stored procedure, you will first create dataset for it. Select stored procedure in dropdown and click refresh fields. If stored procedure has normal select statement from physical table, then you can see all your columns get mapped to the fields but if your stored procedure returns data from temp table then even after clicking on refresh fields, it won't generate fields. OR this stored procedure will behave in the same way when calling it in entity data model (edmx). It won't generate complex datatype as it does for other procedures.

Solution

Not many things to do to solve this problem. You can add the below code within your stored procedure and try to refresh fields.

 /* below code is added to consider temp table. */  
 /*Add this lines at beginning of stored procedure .After BEGIN*/
 /*START*/  
 DECLARE @FMTONLY BIT;  
  
 IF 1 = 0  
 BEGIN  
  SET @FMTONLY = 1;  
  SET FMTONLY OFF;  
 END  
  
 /*END*/

/*Add this lines at end of stored procedure.BEFORE END*/
 /*SET default value */  
 /* START */  
 IF @FMTONLY = 1  
 BEGIN  
  SET FMTONLY ON;  
 END  
   /* END*/

Now check fields tab in dataset properties dialog, if fields are generated, then the problem is solved. For Entity data model, check if complex data type is created or not, if created then no worries.

Even after doing all this stuff, fields are not generated then it means null values are not handled in stored procedure. Actually SSRS and EF passes null values for all parameters when calling stored procedure from designer tool. So, now go to SQL Server and write a logic within the stored procedure to handle null values and check if it returns default output for null values.

For SSRS: Click on refresh fields within dataset property and check fields.

For EF: Delete existing stored procedure and add it from update database model OR you can just update store procedure in model browser.