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

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

Try adding this line to the beginning of your stored procedure:

SET FMTONLY OFF

You can remove this after you have finished importing.

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/

In completeness and simplifying @benshabatnoam's answer, just put the following code at the beginning:

IF (1=2)
    SET FMTONLY OFF

Note: it works in EF 6.1.3 and Visual Studio 2015 Update 3

If you are using temporary table, the Entity (EDMX) cant understand what is going on.

So return empty result with the columns name, comment out all your stored procedure and execute in the sql manager, then get the complex type in visual studio. After saving, return your stored procedure to it's original state (uncommented that is).

good luck/

You're having this problem due to the temp table.

All you need to do is:

1. Alter your stored procedure to return the select statement without the temp table.
2. Go to the function import and get the column information.
3. Alter your stored procedure back to the original.

I'd like to add something to Sudhanshu Singh's answer: It works very well, but if you have more complex structures, combine it with a table declaration.

I have used the following successfully (place it at the very beginning of your stored procecure):

CREATE PROCEDURE [dbo].[MyStoredProc] 
AS
BEGIN

SET NOCOUNT ON;

IF (1=0) -- it never gets executed, but the EF deducts the structure from it
BEGIN 
    SET FMTONLY OFF 
        BEGIN
            -- declaration + dummy query 
            -- to allow EF obtain complex data type:
            DECLARE @MyStoredProcResult TABLE(
                ID          INT,
                VendorName  VARCHAR(255),
                ItemName    VARCHAR(255),
                Type        VARCHAR(2),
                Sequence    TINYINT
                );
            SELECT * FROM @MyStoredProcResult WHERE (1=0)
        END
END   

-- your code follows here (SELECT ... FROM ...)
-- this code must return the same columns/data types
--
-- if you require a temp table / table variable like the one above
-- anyway, add the results during processing to @MyStoredProcResult
-- and then your last statement in the SP can be
-- SELECT * FROM @MyStoredProcResult
END

Note that the 1=0 guarantees that it never gets executed, but the EF deducts the structure from it.

After you have saved your stored procedure, open the EDMX file in Visual Studio, refresh the data model, go to the Entity Frameworks model browser. In the model browser, locate your stored procedure, open up the "Edit Function Import" dialog, select "Returns a collection of ... Complex", then click on the button "Get Column Information".

It should show up the structure as defined above. If it does, click on "Create New Complex Type", and it will create one with the name of the stored procedure, e.g. "MyStoredProc_Result" (appended by "_Result").

Now you can select it in the combobox of "Returns a collection of ... Complex" on the same dialog.

Whenever you need to update something, update the SP first, then you can come back to the Edit Function Import dialog and click on the "Update" button (you don't need to re-create everything from scratch).

As a quick and dirty way to make EF find the columns, comment out the where clause in your stored proc (maybe add a TOP 1 to stop it returning everything), add the proc to the EF and create the Complex Type, then uncomment the where clause again.

I had this issue, what I had to do was create a User-Defined Table Type and return that.

CREATE TYPE T1 AS TABLE 
(  ID      INT,
    VendorName  VARCHAR(255),
    ItemName        VARCHAR(255),
    Type        VARCHAR(2),
    Sequence        TINYINT
);
GO

Your Stored Procedure will now look like this:

ALTER PROCEDURE [healthc].[ev_kc_Products_Search]
(
@SearchString   VARCHAR(1000)
)
AS
SET NOCOUNT ON

DECLARE @SQL    VARCHAR(max),
    @SQL1   VARCHAR(max),
    @Tag    VARCHAR(5)

@T [schema].T1

SET @SQL = 'SELECT  VendorID ID,
    Name VendorName,
    NULL ItemName,
    ''V'' Type,
    0 Sequence
    FROM    tblVendors
    WHERE   '+REPLACE(@SQL1,@Tag,'Name')+'
    UNION ALL
    BLAH BLAH BLAH'

INSERT INTO @T
EXEC(@SQL)

SELECT ID, VendorName, ItemName, Type FROM @T

Just add the select statement without the quotation, execute the stored proc, go get update the model, edit your function import and get column information. This should populate the new columns. Update the result set and go back to your stored proc and remove the select list you just added. And execute the stored proc. This way your columns will get populated in the result set. See below where to add the select list without quote.

    ALTER PROCEDURE [healthc].[ev_kc_Products_Search]
(
    @SearchString   VARCHAR(1000)
)

AS
SET NOCOUNT ON;
SELECT  VendorID ID,
        Name VendorName,
        NULL ItemName,
        ''V'' Type,
        0 Sequence
    FROM    tblVendors

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

I hope this helps someone out there.

This is the only correct answer and can be found from here

https://stackoverflow.com/a/27960583/511273

Basically, EF knows that it's always going to return the number of rows or -1 if NO COUNT is on, or anything returned from the SQL stored procedure that's called by return <some integer>. For that reason, no matter what stored procedure you import, the type will always be nullable<int>. You can only return an integer from an SQL stored procedure. So, EF gives you a way to edit your function. I would imagine that if you edited it manually you would overwrite it on refresh, but I can't confirm that. Either way, this is the facility provided by EF to deal with this issue.

Click on your .emdx file. It has to be the one you selected in the Solution Explorer. Select Model Browser (Right beside Solution Explorer tab, above Properties). Expand Function Imports, locate your stored procedure, right click, click Edit. Select your variable type. It can either be a primitive type or you can click Get Complex Type. Click Get Column Information. I have confirmed this survives a model refresh.

Why can you only return an integer from a stored procedure? I don't really know, but this return definition explains that you can only return an integer: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15