Entity Framework 4: The selected stored procedure returns no columns

Question

In my SP, I mix static SQL and dynamic SQL:

declare @result table
(
 RowNum bigint,
 Id_and_Title varchar(max),
 DaysLeft int,
 cat_id int
);

then, in the dynamic SQL I insert the result to that table:

DECLARE @TSQL NVARCHAR(max);
SET @TSQL = ......

(I use print @TSQL so I'm sure that the query is OK)

insert into @result
EXECUTE sp_executesql @TSQL

select * from @result

but, when I try to import taht SP in VS 2010 Ultimate I see the message as I mentioned in the title. What causes that ? For many times I've occured that error but I still don't know what causes that

Answer 1

Try adding this line to the beginning of your stored procedure:
SET FMTONLY OFF
You can remove this after you have finished importing.

It's mentioned in this article (kind of a long way down) and it worked for me, although I have ended up going back to ADO.NET for the times when I am relying heavily on Stored Procedures. Bear in mind that if your columns change depending on the dynamic SQL your entity model will break.

Answer 2

I don't know exactly what your return type is, but if you have a finite (and relatively small) number of return types, you can create a User-Defined Table Type and return that.

CREATE TYPE T1 AS TABLE 
( ID bigint NOT NULL
  ,Field1 varchar(max) COLLATE Latin1_General_CI_AI NOT NULL
  ,Field2 bit NOT NULL
  ,Field3 varchar(500) NOT NULL
  );
GO

Then in the procedure:

DECLARE @tempTable dbo.T1

INSERT @tempTable (ID, Field1, Field2, Field3)
SELECT .....

....

SELECT * FROM @tempTable

Now EF should be able to recognize the returned columns type.