No fields for dynamic SQL stored procedure in SSRS with SET FMTONLY

Question

I have the following SP which works correctly when ran on its own:

USE [Orders]
GO
SET FMTONLY OFF; 

CREATE PROCEDURE [dbo].[Get_Details_by_Type]

@isArchived varchar(10),
@Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @sqlQuery nvarchar(max)
          IF(@isArchived = 'ALL')
            BEGIN
                set @sqlQuery  = 'SELECT *  FROM [dbo].[Orders] 
              WHERE ' + @Type + ' != € 
                ORDER BY [IDNumber]'
                exec sp_executesql @sqlQuery
            END
        ELSE
            BEGIN
            set @sqlQuery  = 'SELECT * FROM [dbo].[Orders] 
          WHERE ' + @Type + ' != € AND [isArchived] = ' + @isArchived + ' ORDER BY [IDNumber]'
            exec sp_executesql @sqlQuery
        END
END
SET FMTONLY ON; 

The problem I'm having is that when I add a DataSet for a SSRS report, it pulls no fields/columns in the Fields section. I'm guessing it's due to the dynamic SQL?

How can I resolve that?

Answer 1

The Problem
Stored procs which contain Dynamic Sql and Temp tables are the bane of wizards like SSRS and ORM generators like Linq2SQL and EF reverse engineering tools.

This is because the tools SET FMTONLY ON; (or more recently, sp_describe_first_result_set) prior to running the PROC, in order to derive the resultset schema produced by the PROC so that mappings for the ReportViewer UI can be generated. However, neither FMTONLY ON nor sp_describe_first_result actually execute the PROC.

e.g. the tool will do something like:

SET FMTONLY ON;
EXEC dbo.MyProc NULL;

Some Workarounds:

• Manually editing the RDL / RDLC file to insert the actual result set column names and types.
• Temporarily dropping the real proc and substituting it with one which returns a data set of zero or more rows with the actual data types and column names returned by the real proc, running the wizard, and then reverting the real proc.
• Temporarily adding SET FMTONLY OFF; as the first line in the PROC - this will force execution of the PROC. Revert the original PROC once done (although note your proc may fail because of null or dummy parameters passed in by the tool). Also, FMTONLY is being deprecated
• At the start of the proc, adding a dummy statement which returns the actual schema(s) of the result set(s), wrapped in a conditional branch which never gets executed.

Here's an example of the last hack:

CREATE PROCEDURE [dbo].[Get_Details_by_Type]
  @isArchived varchar(10),
  @Type varchar(50)
AS
BEGIN
   -- For FMTONLY ON tools only
   IF 1 = 2
     BEGIN
       -- These are the actual column names and types returned by the real proc
       SELECT CAST('' AS NVARCHAR(20)) AS Col1, 
              CAST(0 AS DECIMAL(5,3)) AS Col2, ...
     END;
-- Rest of the actual PROC goes here

FMTONLY ON / sp_describe_first_result_set are fooled by the dummy conditional and assumes the schema from the never-executed branch.

As an aside, for your own sanity, I would suggest that you don't SELECT * in your PROC - rather explicitly list all the real column names returned from Orders

Finally, just make sure you don't include the SET FMTONLY ON; statement in your proc (from your code above!)

END - Proc
GO **
SET FMTONLY ON; ** This isn't part of the Proc!

Answer 2

If anyone is still facing this issue, I solved what appears to be a similar problem with ssrs and dynamic sql.

1. For ssrs to properly map the fields from the SP,
2. select the "Text" option under query type,
3. enter the SP name and parameters like you are calling it from and SSMS window. sp_YourStoredProc @Parameter1....@ParameterN
4. Click on the refresh fields button.
5. When the statement runs, the fields will refresh, and your matrix will be populated.

BTW, I'm using SQL 2012

Hope this helps.