40

Suppose the following:

CREATE PROCEDURE [MySPROC]
AS 
BEGIN

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) 
    SELECT SomePrimaryKey, SomeColumn 
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn 
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

When I generate a function import or map a return type, EF doesn't generate a complex type or tells me:

The selected stored procedure or function returns no columns

How to overcome this?

Other answers suggest using table variables (not going to do this for performance reasons) faking the return schema and commenting out the real stored procedure, other suggest doing similar with views... but there must be a way to do this without having to add unnecessary overhead or requiring me to break a stored procedure to update the model?

Community
  • 1
  • 1
JoeBrockhaus
  • 2,593
  • 2
  • 37
  • 61

4 Answers4

64
CREATE PROCEDURE [MySPROC]
AS 
BEGIN

--supplying a data contract
IF 1 = 2 BEGIN
    SELECT
        cast(null as bigint)  as MyPrimaryKey,
        cast(null as int)    as OtherColumn
    WHERE
        1 = 2  
END

CREATE TABLE #tempSubset(
    [MyPrimaryKey] [bigint]  NOT NULL,
    [OtherColumn]  [int]     NOT NULL)

INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) 
    SELECT SomePrimaryKey, SomeColumn 
    FROM   SomeHugeTable
    WHERE  LimitingCondition = true

SELECT MyPrimaryKey, OtherColumn 
FROM   #tempSubset
WHERE  SomeExpensiveCondition = true

END

Supplying a faux data contract for the result set is the easiest, cleanest and fastest way to take care of the issue. This same problem exists in data source controls in SSIS too. .NET will read the result set from the unreachable "contract" section of the query and supply the metadata for the complex type. No performance impact and no need to comment out the SQL that does the actual work.

JoeBrockhaus
  • 2,593
  • 2
  • 37
  • 61
BrianKrahenbuhl
  • 664
  • 6
  • 3
  • worked great, and so much better than those alternatives! thanks ;) – JoeBrockhaus May 16 '13 at 18:22
  • Have tested and works great! Will add to a script I use to generate my CREATE PROCEDURES – SleepyBoBos Apr 13 '14 at 23:41
  • 4
    Note that if you generate a Complex Type in your model, after providing data contract in this format, then properties will be nullable ie 'public Nullable MyProperty {get; set;})'. This is fine as it will handle the case where the db column value is null. If you do something like 'SELECT 1 AS [MyProperty column]' in contract the property will be 'public int MyProperty { get; set; }' (you have to actually delete the [sproc]_Result class + complex type and regen complex type for the change in underlying sProc to be seen). Either or dependent on your situation, just be aware. – SleepyBoBos Apr 14 '14 at 03:58
  • @SleepyBoBos, not sure if that has changed, but I've recently made changes like you say, and am able to use the Update functionality of the Function Import Properties window to get changes in Null vs Not Null. – JoeBrockhaus Nov 21 '14 at 15:47
  • To clarify the previous comment, you need to 'Update Model from Database', THEN update the Function Import type. – JoeBrockhaus Apr 28 '15 at 17:25
  • 1
    Hey SleepyBobos and BrianKrahenbuhl Thanks a lot for solution. It worked for me. – t4thilina May 12 '15 at 17:02
  • This isn't working in my tests; I've put the exact code (changed select statement types only) to the top of my proc (after create) and EF is not picking up the return type still. Converting temp tables to table variables did work however. – Slight Dec 18 '15 at 18:14
  • 1
    I wonder if the `trEND` in the answer is supposed to be `true`. – Pang Feb 24 '17 at 04:36
51

Adding this to the top of the stored procedure definition: 

SET FMTONLY OFF
allowed the model to infer the schema from the temporary table without issue. As a bonus, it doesn't require additional maintenance for a contract.

Example: 

SET FMTONLY OFF

CREATE TABLE #tempTable (
    ...
)

...

SELECT * FROM #tempTable
RominNoodleSamurai
  • 654
  • 10
  • 17
10

Solution 1 Use a table variable instead of a temporary table.

Solution 2 Use the Set FMTONLY off; SQL command in the procedure and you will get the column information to create a new complex type.

Solution 3 This is not a good way, but it's a very easy way. Just add a select statement with dummy data and it will not execute because 1=0.

you can check details on this link

user3364545
  • 113
  • 2
  • 7
  • 1
    I have tried all 3 approaches. None of them worked. Since I am always 2 day late, and I have spent 2 hours on this. I need to move on. My temporary solution is going to view model detail to change the composite type (find the procedure and variable) and manually change from int32 to string. It is a bad idea. If refresh, it will lost. I cannot even document it to remind other developers. Anyone finds out what happens. Please let everyone know. I am using VS 2015, EF 6, SQL server 2016. TX – user12345 Dec 01 '17 at 19:14
0

This is incomplete but when set fmtonly off does not work, you can generate the data contract using the following:

        SELECT * 
        FROM tempdb.sys.columns 
        WHERE [object_id] = OBJECT_ID(N'tempdb..#u');

        select case  system_type_id 
        when 62 then 'cast(null as float) as ' 
        when 175 then 'cast(null as char(' + cast(max_length as varchar(50)) + ')) as ' 
        when 167 then 'cast(null as varchar(' + cast(max_length as varchar(50)) + ')) as ' 
        when 56 then 'cast(null as int) as ' 
        when 104 then 'cast(null as bit) as ' 
        when 106 then 'cast(null as decimal(' + cast(precision as varchar(50)) + ',' + cast(scale as varchar(50)) + ')) as ' 
        when 40 then 'cast(null as date) as '            
        end
        + name + ','
        from  tempdb.sys.columns 
        WHERE [object_id] = OBJECT_ID(N'tempdb..#u');
Mark Pearson
  • 66
  • 2