SET FMTONLY OFF effects on stored procedures with dynamic query

Question

I use Telerik Report designer R1 2017, and use a stored procedure with dynamic query as data source. It appears the schema cannot be obtained from the dynamic query. I Googled and found out that it's not possible without adding the following lines in the beginning of the stored procedure

IF 1 = 0 BEGIN
 SET FMTONLY OFF
END

But I have really slow execution in this way! I'm not very familiar with store procedure execution process. I want to know What negative effects can it (SET FMTONLY OFF) have?

Answer 1

If you are using SQL Server 2012+ you could use WITH RESULT SETS to define resultset:

CREATE OR ALTER PROCEDURE dbo.mysp_test
AS
BEGIN

   DECLARE @sql NVARCHAR(MAX) = 'SELECT id, b FROM dbo.tab  ' + 'WHERE 1=1';

   EXEC sp_executesql @sql

END
GO

SELECT *
FROM sys.dm_exec_describe_first_result_set (
  'EXEC dbo.mysp_test'
  ,NULL
  ,NULL
);

The metadata could not be determined because statement 'EXEC sp_executesql @sql' in procedure 'mysp_test' contains dynamic SQL.

Using WITH RESULT SETS:

CREATE OR ALTER PROCEDURE dbo.mysp_test
AS
BEGIN

   DECLARE @sql NVARCHAR(MAX) = 'SELECT id, b FROM dbo.tab  ' + 'WHERE 1=1';

   EXEC sp_executesql @sql
   WITH RESULT SETS(  
        (id INT NOT NULL,
         b CHAR(1)
        )  
    ); 
END
GO

SELECT *
FROM sys.dm_exec_describe_first_result_set (
  'EXEC dbo.mysp_test'
  ,NULL
  ,NULL
);

DBFiddle Demo