Expose APIs to select distinct matching values for a filtered table-or-view column

[CalvinQuark]

It would be useful if generated APIs facilitated submission of a complex hierarchical request to a GraphQL endpoint with predicates applied at different levels in the hierarchy and with the response containing only the distinct values for a specified top-level column drawn from records which satisfy the predicate hierarchy. I.e., the column's distinct values in the context of the applied filters.

I have started to work around this issue by creating a single generic stored procedure for all of the exposed tables in the database. My intention is guard the execution of this SPROC using the same T-SQL logic that I also use to generate the app-specific entities node in the dab-config.json and staticwebapp.database.config.json files.

For reference, here is the as-yet incomplete implementation:

CREATE PROCEDURE Utility.p_GetDistinctTableColumnValues
    @SchemaName     AS SYSNAME,
    @TableName      AS SYSNAME,
    @ColumnName     AS SYSNAME,
    @SqlWhereClause AS NVARCHAR(MAX) = NULL,
    @SortAscending  AS BIT = 1,
    @TopTake        AS INT = 100 -- Enforced maximum is 100
AS
BEGIN
    DECLARE @IsDevBox BIT;
    IF SERVERPROPERTY('MachineName') = N'MY-DEV-BOX-NAME' -- solopreneur hack
        SET @IsDevBox = 1
    ELSE
        SET @IsDevBox = 0;
  --END IF

    IF @TopTake > 100 SET @TopTake = 100;

  -- TODO: Develop proper guards for the `@SqlWhereClause` parameter

  -- TODO: Develop and incoporate `SET @IsAllowed = Utility.f_IsAllowedToDistinctFetchValuesFor(AppName, RoleName, SchemaName, TableName, ColumnName);`

    DECLARE @SQL AS NVARCHAR(MAX) = CONCAT(N'SELECT DISTINCT TOP(', @TopTake, N') [', @ColumnName, N']', Utility.f_CrLf(),
    N'FROM [', @SchemaName, N'].[', @TableName, N']', Utility.f_CrLf(),
    IIF(@IsDevBox = 0 OR @SqlWhereClause IS NULL OR TRIM(@SqlWhereClause) = N'', N'', @SqlWhereClause + Utility.f_CrLf()),
    N'ORDER BY [', @ColumnName, N'] ', IIF(@SortAscending = 1, N'ASC', N'DESC'), Utility.f_CrLf(),
    N'FOR JSON PATH, INCLUDE_NULL_VALUES;');

    EXEC sys.sp_executesql @stmt = @Sql
    WITH RESULT SETS ((JsonArray NVARCHAR(MAX)));
END
GO

If the API Builder generated these Select Distinct APIs, they would likely be used in various ways. One way would be to support a panel of filter widgets where each filter widget requests distinct values for a particular column predicated by all the other applied filters. I.e., they are all targeting different columns of the same table or view.