dotnet / dotnet/SqlClient

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

Open
#4,470 1 comment 0 reactions 1 assignee Claimed by @paulmedynski View on GitHub
Area\Json Repro Available :heavy_check_mark:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 18h
Merged PRs (30d)
69

Description

### 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

```c#
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.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.