Entity Framework cannot handle a simple table variable?

Question

• Last line of the stored procedure: select * from @t
• The model was updated and it found a stored procedure
• Tried to import a new function using the wizard, and he said that no columns could be found.

Really? Someone tells me this is lying.

 create procedure WorkIt as set nocount on create table #pivot ( Name varchar(30), Value decimal, Grade varchar(2) ) insert into #pivot select 'Repeating Pct', 1, 'K' union all select 'Repeating Pct', 2, '1' union all select 'Repeating Pct', 3, '2' union all select 'Repeating Pct', 4, '3' union all select 'Repeating Pct', 5, '4' union all select 'Repeating Pct', 6, '5' union all select 'Repeating Pct', 7, '6' union all select 'Repeating Pct', 8, '7' union all select 'Repeating Pct', 9, '8' union all select 'Repeating Pct', 10, '9' union all select 'Repeating Pct', 11, '10' union all select 'Repeating Pct', 12, '11' union all select 'Repeating Pct', 13, '12' declare @t table ( name varchar(30), K decimal (15,5) , [1] decimal (15,5), [10] decimal (15,5), [11] decimal (15,5), [12] decimal (15,5), [2] decimal (15,5), [3] decimal (15,5), [4] decimal (15,5), [5] decimal (15,5), [6] decimal (15,5), [7] decimal (15,5), [8] decimal (15,5), [9] decimal (15,5) ) insert into @t exec dbo.CrossTabWithoutSumWithOrderBy #pivot, 'Name', null, 'Grade', 'Value', -- sort repeating pct to bottom 'case name when ''Repeating Pct'' then 999 else 0 end' drop table #pivot select * from @t 

Result

 name K 1 10 11 12 2 3 4 5 6 7 8 9 Repeating Pct 2.00000 11.00000 12.00000 13.00000 3.00000 4.00000 5.00000 6.00000 7.00000 8.00000 9.00000 10.00000 1.00000 

Answer 1

When an entity structure tries to get columns from a stored procedure, it calls SET FMTONLY ON , and then executes the stored procedure. When FMTONLY - ON execution returns only metadata and does not work with some advanced design in stored procedures - for example, dynamic SQL, temporary tables, and also variable tables.

You have three options:

• As described in another answer, add SET FMTONLY OFF at the beginning of the stored procedure. This will cause your stored procedure to actually execute, so make sure it only reads the data - any insert, update or delete will be performed every time you try to get the columns!
• Manually identify a complex type
• Modify the stored procedure to not use any of these functions.