1

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?

Return parameter

2

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.