Ability to reverse engineer stored procedures using temp tables.

[nlarion]

Perhaps this is a known issue, and in that case apologies... It would be nice to have the ability to reverse engineer stored procedures that create and use temp tables. When I Reverse Engineer a database with stored procedures that include a temp table, the extension throws an error:

The metadata could not be determined because statement 'INSERT #x (something, somethingelse)
SELECT 'wow', GETDATE()' in procedure 'test' uses a temp table.
Unable to scaffold UpdateIncomingSpec

Steps to reproduce

• Reverse Engineer a database
• include stored procedures
• use a stored procedure that includes a temp table.
• example sproc:

CREATE PROC [dbo].[test]
	@DetailID INT,
	@returnCode INT OUTPUT,
	@result VARCHAR (MAX) OUTPUT
AS

SET NOCOUNT ON

CREATE TABLE #x (
	something VARCHAR (MAX) NOT NULL,
	somethingelse VARCHAR (MAX) NOT NULL
	);

INSERT #x (something, somethingelse)
SELECT 'wow', GETDATE()

BEGIN
	SELECT @ReturnCode = 0, @Result = '';
END

DROP TABLE #x;
GO

Further technical details

EF Core Power Tools version: 2.4.236.0

Database engine: SQL Server

Visual Studio version: VS2019 16.7.5

[ErikEJ]

I am using sp_describe_first_result_set to discover metadata, and it does not support temp tables, so this is sadly by design.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql?view=sql-server-ver15

[tiomny]

Please check https://github.com/DarioN1/SPToCore. It can generate stored procedure mapping even if an SP uses temp table or contains dynamic code.
Such generated stored procedure mappings are fine in some cases, for example when I don't need results set.

[ErikEJ]

@tiomny I see, so you suggest just logging and then ignoring errors callings sp_describe_first_result_set ?

[tiomny]

That might be the parameter IgnoreDescribeResultSetError.

[ErikEJ]

That might be the parameter IgnoreDescribeResultSetError.

I cannot see that anywhere, could you please link to it?

[tiomny]

I suggest you to introduce this parameter and corresponding functionality.

[ErikEJ]

I think it may make more sense to just ignore and log the failed attempt to get the result set?

[tiomny]

That would work for me perfectly.

[ErikEJ]

Fixed in latest daily build

[facurodriguez]

sorry to bother but I'm getting the same error with the following configuration:
EF Core Power Tools version: 2.5.513
Database engine: SQL Server
Visual Studio version: VS2019 16.8.5

[ErikEJ]

There is no fix, but an empty result class is created, and you can manually add the properties.

[GearTheWorld]

This is a HUGE issue. We hope for a solution either by Microsoft or by Erik !

I tried all the imaginable possibilities even declaring variables and adding the values of the select into the variables and doing a SELECT of these variables at the end

[ErikEJ]

@GearTheWorld If the solutions proposed above do not work for you, please create a new issue with FULL repro information, and I will have a look.

[0liver]

To help myself in the future when I hit this problem again, here's the solution for creating the proper .NET result type during reverse engineering, when you actually want that type to be correctly created:

create procedure procs.MyProc
as
begin
    -- workaround for ef core: define result type explicitly
    if not exists(select sessionproperty('fmtonly'))
    begin
        set fmtonly off; --   <-- This is the key!

        select top (0)
            0 as PropInt,
            '' as PropString,
            cast(0 as bit) as PropBool;

        return;
    end

    -- create temporary table
    create table #myTemp (
        -- columns here
    );

    -- more code
end

[ErikEJ]

@0liver Thanks - I will add a link to this in the docs