SQLCommandBuilder.DeriveParameters() having trouble with StoredProc Parameters of Type JSON

[niphlod]

Describe the bug

The introduction of a JSON datatypes dates back to "some months ago" in Azure SQL, but now it also landed on 2025.

We have SqlDbType.Json, a stored procedure accepting a JSON parameter or having a JSON OUT parameter compiles correctly, can be executed without issues so I guess .... DeriveParameters should work.

Now, SQLCommandBuilder.DeriveParameters() seems to rely on the underlying [sys].[sp_procedure_params_100_managed], which in turn (at least on SQL 2025 where is readable) is based upon sys.fn_procedure_params_90_rowset .

Summing up, I think the main problem is the underlying support on SQL itself, but in the end, SQLCommandBuilder.DeriveParameters() completely skips over any JSON parameter of a sproc.

To reproduce

using System.Data;
using Microsoft.Data.SqlClient;

string storedProcedureBody = """
    CREATE OR ALTER PROCEDURE dbo.givemeoutput
    (
    	@somevarchar varchar(5),
    	@someint int OUTPUT,
        @somejson JSON OUTPUT
    )
    AS
    BEGIN
    	SELECT somevarchar = @somevarchar;
        SET @someint = COALESCE(@someint, 0) + 2;
        SET @somejson = (SELECT a = 1, b = 'b', c = @someint FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
        RETURN 6;
    END
    """;

string connectionString =
    "Data Source=....";

using SqlConnection connection = new(connectionString);
await connection.OpenAsync();
using SqlCommand commandCreateSproc = new(storedProcedureBody, connection);
await commandCreateSproc.ExecuteNonQueryAsync();

using SqlCommand commandExecuteSproc = new("dbo.givemeoutput", connection);
commandExecuteSproc.CommandType = CommandType.StoredProcedure;
SqlParameter someintParam = new("@someint", SqlDbType.Int) { Direction = ParameterDirection.Output };
SqlParameter somejsonParam = new("@somejson", SqlDbType.Json, -1) { Direction = ParameterDirection.Output };
SqlParameter somevarcharParam = new("@somevarchar", SqlDbType.VarChar, 5) { Direction = ParameterDirection.Input, Value = "abcde" };
commandExecuteSproc.Parameters.AddRange([someintParam, somejsonParam, somevarcharParam]);

await commandExecuteSproc.ExecuteNonQueryAsync();

Console.WriteLine($"Output int: {someintParam.Value}");
Console.WriteLine($"Output JSON: {somejsonParam.Value}");

using SqlCommand commandDeriveParameters = new("dbo.givemeoutput", connection);
commandDeriveParameters.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(commandDeriveParameters);

Console.WriteLine("Derived Parameters:");
foreach (SqlParameter p in commandDeriveParameters.Parameters)
{
    Console.WriteLine($"    Parameter Name: {p.ParameterName}");
    Console.WriteLine($"    Parameter Type: {p.SqlDbType}");
    Console.WriteLine($"    Parameter Direction: {p.Direction}");
}

using SqlCommand commandDrop = new("DROP PROCEDURE dbo.givemeoutput", connection);
await commandDrop.ExecuteNonQueryAsync();

Expected behavior

Test above ends up not considering at all the JSON parameter ... I'd like for "somejson" to be returned, too

Output int: 2
Output JSON: {"a":1,"b":"b","c":2}
Derived Parameters:
    Parameter Name: @RETURN_VALUE
    Parameter Type: Int
    Parameter Direction: ReturnValue

    Parameter Name: @somevarchar
    Parameter Type: VarChar
    Parameter Direction: Input

    Parameter Name: @someint
    Parameter Type: Int
    Parameter Direction: InputOutput

Further technical details

Microsoft.Data.SqlClient version: 7.0.2
.NET target: .net 10
SQL Server version: SQL Server 2025 (or Azure SQL Database)
Operating system: All

Additional context
Add any other context about the problem here.

[github-actions]

🔍 Triage Summary

Check	Result
Issue type	Bug
Environment	All required environment details provided for investigation
Area	Area\Json
Duplicates	None found
Regression	Not indicated — this is a gap in new JSON type support

Analysis

SqlCommandBuilder.DeriveParameters() silently skips stored procedure parameters of type JSON (SQL Server 2025 / Azure SQL). The root cause appears to be in the server-side system procedure sys.sp_procedure_params_100_managed (backed by sys.fn_procedure_params_90_rowset), which does not return rows for the new JSON type — but the client-side code in SqlCommandBuilder also lacks mapping logic to translate any JSON type token it might receive into SqlDbType.Json. This is a P2 bug affecting users targeting SQL Server 2025 and Azure SQL with the new native JSON type.

Next Steps

• Assign this issue to the Copilot coding agent to investigate SqlCommandBuilder.DeriveParameters (and its async counterpart SqlCommandBuilder.DeriveParametersAsync) in SqlCommandBuilder.cs. The fix likely requires:
  1. Examining what type token/name sp_procedure_params_100_managed returns for JSON parameters (it may return the underlying type or simply omit it).
  2. Adding handling in the parameter-row parsing loop to recognize the JSON type name/token and map it to SqlDbType.Json.
  3. If the server-side sproc omits JSON rows entirely, consider a fallback query (e.g., querying sys.parameters joined with sys.types) as a workaround for SQL Server 2025+.
• Add a manual test covering a stored procedure with a JSON-typed parameter to verify DeriveParameters returns the expected SqlDbType.Json entry.

Note: This triage summary is auto-generated by an AI agent. The analysis and suggestions above have not been verified by a human maintainer. Please treat as preliminary guidance only.

Generated by SqlClient Issue Auto-Triage for issue #4470 · 30.8 AIC · ⌖ 8.98 AIC · ⊞ 8.4K · ◷