External content type from SQL stored procedure with EXEC

Question

I'm trying to create an external content type from a stored procedure. I'm hitting one problem, when creating a Read List Operation, though:

The wizard is not able to find any return parameters (see image), when my stored procedure ends with EXEC:

CREATE PROCEDURE getData
AS
BEGIN
  DECLARE @sql NVARCHAR ( MAX);
  EXEC @sql;
END

Now, if I use a common select, SharePoint is able to recognize return parameters:

CREATE PROCEDURE getData
AS
BEGIN
  SELECT * FROM data_table;
END

I need to use EXEC, as I use a lot of metadata in my procedure. Is this a limitation of Sharepoint? Can I get a workaround?

Answer 1

As a workaround, you could save the results of the EXEC into a table, and return that:

CREATE PROCEDURE getData
AS
BEGIN
  DECLARE @sql NVARCHAR ( MAX);
  DECLARE @table TABLE (
    field1 INT, ...
  );

  INSERT INTO @table (field1, ... )
    EXEC @sql;

  SELECT * FROM @table;
END