[Bug]: `numeric` data type not supported as a parameter for a stored procedure

[Aniruddh25]

What happened?

System.Data.Common.SqlDbType https://learn.microsoft.com/en-us/dotnet/api/system.data.sqldbtype?view=net-8.0 doesn't have a Numeric type, thats why we unfortunately see this error. The autodetection is done by querying sys.dm_exec_describe_first_result_set which gives back numeric from SQL. We will need to handle this discrepancy between these dependencies in DAB explicitly for this and other possible types which may be absent in System.Data.Common.SqlDataType

Version

main

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[chrispharmac]

Here is a simple minded patch which got us around this issue. It's weird that Microsoft don't include numeric in their enum.

.../Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs  | 1 +
 data-api-builder-1.2.10/src/Core/Services/TypeHelper.cs           | 8 ++++++++
 .../src/Service.Tests/DatabaseSchema-MsSql.sql                    | 5 +++--
 3 files changed, 12 insertions(+), 2 deletions(-)

diff --git a/data-api-builder-1.2.10/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs b/data-api-builder-1.2.10/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs
index d7d900d..5c978a1 100644
--- a/data-api-builder-1.2.10/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs  
+++ b/data-api-builder-1.2.10/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs  
@@ -438,6 +438,7 @@ namespace Azure.DataApiBuilder.Core.Resolvers
                 "Single" => float.Parse(param),
                 "Double" => double.Parse(param),
                 "Decimal" => decimal.Parse(param),
+                "Numeric" => decimal.Parse(param),
                 "Boolean" => bool.Parse(param),
                 // When GraphQL input specifies a TZ offset "12-31-2024T12:00:00+03:00"
                 // and DAB has resolved the ColumnDefinition.SystemType to DateTime,
diff --git a/data-api-builder-1.2.10/src/Core/Services/TypeHelper.cs b/data-api-builder-1.2.10/src/Core/Services/TypeHelper.cs
index 56b86b8..8206f7a 100644
--- a/data-api-builder-1.2.10/src/Core/Services/TypeHelper.cs
+++ b/data-api-builder-1.2.10/src/Core/Services/TypeHelper.cs
@@ -8,6 +8,7 @@ using System.Net;
 using Azure.DataApiBuilder.Core.Services.OpenAPI;
 using Azure.DataApiBuilder.Service.Exceptions;
 using HotChocolate.Language;
+using HotChocolate.Utilities;
 using Microsoft.OData.Edm;

 namespace Azure.DataApiBuilder.Core.Services
@@ -147,6 +148,7 @@ namespace Azure.DataApiBuilder.Core.Services
                 "Single" => EdmPrimitiveTypeKind.Single,
                 "Double" => EdmPrimitiveTypeKind.Double,
                 "Decimal" => EdmPrimitiveTypeKind.Decimal,
+                "Numeric" => EdmPrimitiveTypeKind.Decimal,
                 "Boolean" => EdmPrimitiveTypeKind.Boolean,
                 "DateTime" => EdmPrimitiveTypeKind.DateTimeOffset,
                 "DateTimeOffset" => EdmPrimitiveTypeKind.DateTimeOffset,
@@ -189,6 +191,7 @@ namespace Azure.DataApiBuilder.Core.Services
                 "ID" => EdmPrimitiveTypeKind.Guid,
                 "Int" => EdmPrimitiveTypeKind.Int32,
                 "Float" => EdmPrimitiveTypeKind.Decimal,
+                "Numeric" => EdmPrimitiveTypeKind.Decimal,
                 "Boolean" => EdmPrimitiveTypeKind.Boolean,
                 "Date" => EdmPrimitiveTypeKind.Date,
                 _ => EdmPrimitiveTypeKind.PrimitiveType
@@ -269,6 +272,11 @@ namespace Azure.DataApiBuilder.Core.Services
             // Remove the length specifier from the type name if it exists.Example: varchar(50) -> varchar
             int separatorIndex = sqlDbTypeName.IndexOf('(');
             string baseType = separatorIndex == -1 ? sqlDbTypeName : sqlDbTypeName.Substring(0, separatorIndex);
+            if (baseType.EqualsInvariantIgnoreCase("numeric"))
+            {
+                // SqlDbType includes decimal, but not its synonym numeric
+                baseType = "Decimal";
+            }

             if (Enum.TryParse(baseType, ignoreCase: true, out SqlDbType sqlDbType))
             {
diff --git a/data-api-builder-1.2.10/src/Service.Tests/DatabaseSchema-MsSql.sql b/data-api-builder-1.2.10/src/Service.Tests/DatabaseSchema-MsSql.sql
index 7eeb3d2..a21e46a 100644
--- a/data-api-builder-1.2.10/src/Service.Tests/DatabaseSchema-MsSql.sql
+++ b/data-api-builder-1.2.10/src/Service.Tests/DatabaseSchema-MsSql.sql
@@ -227,7 +227,8 @@ CREATE TABLE type_table(
     smalldatetime_types smalldatetime,
     time_types time,
     bytearray_types varbinary(max),
-    uuid_types uniqueidentifier DEFAULT newid()
+    uuid_types uniqueidentifier DEFAULT newid(),
+    numeric_types numeric(38, 19)
 );

 CREATE TABLE trees (
@@ -276,7 +277,7 @@ CREATE TABLE series (
 CREATE TABLE sales (
     id int NOT NULL IDENTITY(5001, 1) PRIMARY KEY,
     item_name varchar(max) NOT NULL,
-    subtotal decimal(18,2) NOT NULL,
+    subtotal numeric(18,2) NOT NULL,
     tax decimal(18,2) NOT NULL
 );