Why doesn't the Entity Framework see my stored procedure footer?

Question

I have the following stored procedure, and when I try to execute the Import function, it says that my stored procedure does not return 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 top of the stored procedure:

 SET FMTONLY OFF 

You can delete this after import is complete.

Answer 2

What is going on here backstage?

• When you execute the import → Get Column Information ... function, Visual Studio executes the stored process with all parameter values ​​as NULL (you can cross-check this via MS SQL Profiler).

• In step 1, the columns saved as a result are returned with the data type and information length.

• After receiving information about the column, clicking the Create New Complex Type button creates a complex SP type in conflict.

In your case, the stored parameters of proc are not null, so the call to Visual Studio fails and does not return 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   

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

Answer 3

in full and simple @benshabatnoam answer, just enter 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

Answer 4

You are having this problem due to a temporary table. All you have to do is: 1. Modify the stored procedure to return the selected statemant without the temp table. 2. Go to the import function and get information about the column. 3. Change the stored procedure back to the original.

Answer 5

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

Answer 6

I would 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 successfully used the following (put it at the very beginning of the stored procedure):

 CREATE PROCEDURE [EY].[MyStoredProc] AS BEGIN SET NOCOUNT ON; IF (1=0) 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 1=0 ensures that it will never be executed, but EF subtracts the structure from it.

After you saved the stored procedure, open the EDMX file in Visual Studio, update the data model, go to the Entity Frameworks model browser. In the model’s browser, find your stored procedure, open the “Edit Import Function” dialog box, select “Returns the collection ... Complex”, then click the “Get Column Information” button.

It should display the structure defined above. If so, click Create New Complex Type, and it will create one with the name of the stored procedure, for example. "MyStoredProc_Result" (added by "_Result").

Now you can select it in the "Returns collection ... Complex" drop-down list in the same dialog box.

Whenever you need to update something, first update the SP, then you can return to the "Edit Import Function" dialog box and click the "Update" button (you do not need to recreate everything from scratch).

Answer 7

If you use a temporary table, the Entity entity (EDMX) does not understand what is happening.

So, return an empty result with the name of the columns, comment out your entire stored procedure and execute it in the SQL manager, then get the complex type in the visual studio. After saving, return the stored procedure to its original state (without injury).

good luck /

Answer 8

I had this problem, I needed to create a custom table type and return it.

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

Your saved procedure will 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 

Answer 9

Just add the select statement without a quote, execute the stored procedure, start the model update, edit the function import and get the column information. This should fill in the new columns. Refresh the result set and return to the saved process and delete the selection list you just added. And follow the stored procedure. This way your columns will be populated in the result set. See below for a selection list without a 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.