SqlParameter Output returns null

Question

I have a stored procedure that lists items starting at an @index and returns the next @length items. In addition, an @available Output parameter returns the count of items in the set.

CREATE PROCEDURE dbo.List
    @index int = 0,
    @lenth int = 10,
    @available int OUTPUT
AS
BEGIN
    SELECT * FROM dbo.table1 WHERE ...;
    @available = (SELECT COUNT(*) FROM dbo.table1);
END

table1 contains 12 rows.

I run some.NET code (C#) using System.Data.SqlClient to get the data:

await using var connection = new SqlConnection("Server=...");

await connection.OpenAsync();

await using var command = new SqlCommand("dbo.List", connection) { CommandType = CommandType.StoredProcedure };

command.Parameters.Add(new SqlParameter("index", SqlDbType.Int) { Value = 0 });
command.Parameters.Add(new SqlParameter("length", SqlDbType.Int) { Value = 3 });

var outParam = new SqlParameter("available", SqlDbType.Int) { Direction = ParameterDirection.Output };

command.Parameters.Add(outParam);

await using var reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    Console.WriteLine(reader["Name"]);
}

Console.WriteLine("Available items: " + outParam.Value + " rows");

This outputs something like

Joe
Anne
Raj
Available items:  rows

For some reason the outParam.Value returns null, while the expected result is 12.

Anyone have insight into what is going on here? Where did my "rows count" go?

Answer 1

Parameter name passed to SqlParameter ctor should match the one of the stored procedure, try changing the code to:

var outParam = new SqlParameter("@available", SqlDbType.Int) 
{ 
   Direction = ParameterDirection.Output 
};

Also AFAIK reader should be closed so the output parameter can be processed. One way to fix this is to switch from using declaration (which will result in reader being disposed at the end of the holding scope, i.e. after outParam is accessed) to using statement:

await using (var reader = await command.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())
    {
        Console.WriteLine(reader["Name"]);
    }
}

Console.WriteLine("Available items: " + outParam.Value + " rows");