Why can't Entity Framework see my Stored Procedure's column information?

Question

I have the following stored procedure and when I attempt to Function Import it says my Stored Procedure returns no columns. What am I missing? Any Suggestions?

The Proc:

ALTER PROCEDURE [healthc].[ev_kc_Products_Search] (     @SearchString   VARCHAR(1000) ) AS SET NOCOUNT ON      DECLARE @SQL    VARCHAR(max),         @SQL1   VARCHAR(max),         @Tag    VARCHAR(5)      CREATE TABLE #T     (   ID      INT,         VendorName  VARCHAR(255),         ItemName        VARCHAR(255),         Type        VARCHAR(2),         Sequence        TINYINT     )    SET @SQL = '      INSERT  #T      SELECT  VendorID ID,         Name VendorName,         NULL ItemName,         ''V'' Type,         0 Sequence     FROM    tblVendors     WHERE   '+REPLACE(@SQL1,@Tag,'Name')+'      UNION ALL      BLAH BLAH BLAH'    EXEC(@SQL)    SELECT ID, VendorName, ItemName, Type FROM #T 

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.

Answer 2

Whats happening here behind the scenes?

1. While doing function import -> Get Column Information ... Visual Studio executes the stored proc with all the param values as NULL (you can cross-check this through MS SQL Profiler).

2. Doing step 1, the stored proc's resulting columns are returned with its data type and length info.

3. Once the column info is fetched, clicking on 'Create New Complex Type' button creates the Complex type of the SP in contention.

In your case, the stored proc params are not nullable, hence the Visual Studio call fails and returns no columns.

How to handle this?

 IF (1=0)  BEGIN      SET FMTONLY OFF      if @param1 is null and @param2 is null then         begin             select             cast(null as varchar(10)) as Column1,             cast(null as bit) as Column2,             cast(null as decimal) as Column3         END END    

To be precise (in your case):

 IF (1=0)  BEGIN      SET FMTONLY OFF      if @SearchString is null then         BEGIN             select             cast(null as int) as ID,             cast(null as varchar(255)) as VendorName,             cast(null as varchar(255)) as ItemName,             cast(null as varchar(2)) as Type         END END    

Reference: http://mysoftwarenotes.wordpress.com/2011/11/04/entity-framework-4-%E2%80%93-the-selected-stored-procedure-returns-no-columns-part-2/