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

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);

CodePudding user response：

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;
}

CodePudding user response：

You should use a Parameter with the Direction property set to ReturnValue, and, inside the sp, declare an internal variable and set it to the value you want.
Then call the RETURN statement before leaving the StoredProcedure.

As an example, see this SP:

ALTER PROCEDURE [GetTimeZoneGMT]
    @TimeZone NVARCHAR(128)
AS
BEGIN
 DECLARE @timeZoneNumber as INT = -20;
 IF @TimeZone ='Pacific/Midway'
    SET @timeZoneNumber = -11
 ELSE IF @TimeZone ='Pacific/Niue'
    SET @timeZoneNumber = -11
 ELSE IF @TimeZone ='Pacific/Pago_Pago'
    SET @timeZoneNumber = -11

 SELECT 1 -- or whatever you need to have as result set

 RETURN @timeZoneNumber;
END

The stored procedure ends with a (bogus) SELECT statement but also has a RETURN statement with the parameter set inside the SP logic.

Now from the C# side you could call it in this way (LinqPad example)

using (var connection = new SqlConnection("Data Source=(LOCAL);Initial Catalog=LinqPADTest;Integrated Security=True;"))
{ 
    connection.Open();
    SqlCommand cmd = new SqlCommand("GetTimeZoneGMT", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@TimeZone", SqlDbType.NVarChar).Value = "Asia/Kuala_Lumpur";

    SqlParameter r = cmd.Parameters.Add("@p2", SqlDbType.BigInt);
    r.Direction = ParameterDirection.ReturnValue;
    
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    
    r.Value.Dump();  // Prints -20
    dt.Dump();       // Prints a row with a single column with 1 as value     
}