How to get output parameter and also a table when executing a stored procedure

Scenario

• I’m working with SQL Server 2017 (not possible to change)
• I’m using Visual Studio 2019 in C# console and .NET Framework 4.5 (possible to change)
• I’m using ADO.NET because several years before we couldn’t use Entity Framework, as the system is made to work with a stored procedure that returns at least 100k rows (possible to change)

Situation

I have an USP that returns a table that is at least 100k of rows by 20 fields. I need to add an output parameter in order to get also an ID created by the USP itself. So, the situation is that I need return a table and an ID (called ProcMonitorId). I don’t know if this is even so possible (See workarounds section)

At the SQL level is seems to be so far so good:

CREATE PROCEDURE [myschema].[mystore]
    @ProcMonitorId BIGINT OUTPUT
AS
BEGIN
BEGIN TRANSACTION
(...)

    SELECT fields FROM myTable

    SELECT @ProcMonitorId = @internalVariable

SQL execution:

And at repository layer (only relevant lines, someone were surprised for health of example):

var command = new SqlCommand("myStoreProcedure", mycon);
command.CommandType = CommandType.StoredProcedure;
       
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@ProcMonitorId";
outPutParameter.SqlDbType = System.Data.SqlDbType.BigInt;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(outPutParameter);

// Open connection etc-etc that works
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
string ProcMonitorId = outPutParameter.Value.ToString();

da.Fill(dt);

Everything worked fine until the addition of the output at C# level. It returns in the line:

string ProcMonitorId = outPutParameter.Value.ToString();

it returns NullReferenceException because Value is null (that can’t be) and of course, can’t convert to String. I would solve this situation by adding a ? but if that’s situation happens for real, I need catch it any way as error. The main idea is that Value can not be null.

As I don’t have any ORM map, (and my expertise is not ADO.NET but Entity Framework) I can’t understand why is null (No, is not null at SQL layer, always return a value)

Question

How can I solve this error or how can I return a BIGINT parameter and ALSO a table result?

Workarounds

As I first glance I have to solve it quickly, I made a:

SELECT 1 as type, @procID as procid, null as data1, null as data2
UNION ALL
SELECT 2 as type, null as procid, data1, data2

in order to simulate a “header” and “data” rows on one single table. But I don’t like this solution and is not very elegant and flexible. I’ve to parse the header every time.

Thanks in advance and please comment anything, tip, help, workaround, I will be glade to update my answer if more information is needed.

Also I can make my Framework to .NET Core or change to Entity Framework. That I can’t change is my SQL version

Update #2
No changes in SQL – Still working as screenshot
In C# – Hangs out for ever

    SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DbConnection"]);

    connection.Open();
    var command = new SqlCommand("myUSP", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.CommandTimeout = Convert.ToInt16(ConfigurationManager.AppSettings["DataBaseTimeOut"]);
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

        SqlParameter r = command.Parameters.Add("@ProcMonitorId", SqlDbType.BigInt);
        r.Direction = ParameterDirection.Output;

        DataTable dt = new DataTable();
        using (var rdr = command.ExecuteReader())
        {
            dt.Load(rdr);
            long id = (long)r.Value;
        }

        SqlDataAdapter da = new SqlDataAdapter(command);

        da.Fill(dt);

Answer

The parameter value won’t be available until after you consume the resultset, eg

var cmd0 = new SqlCommand("create or alter procedure pFoo @id int output as begin  select * from sys.objects; set @id = 12; end", con);
cmd0.ExecuteNonQuery();

var cmd = new SqlCommand("pFoo", con);
cmd.CommandType = CommandType.StoredProcedure;

var p1 = cmd.Parameters.Add("@id", SqlDbType.Int);
p1.Direction = ParameterDirection.Output;


var dt = new DataTable();
using (var rdr = cmd.ExecuteReader())
{                    
    dt.Load(rdr);
    var id = (int)p1.Value;
}