How to create a dynamic order by query with with multiple fields

Question

I am trying to create a sproc with multiple order by fields. The order by field and direction is passed through the sproc as arguments.

CREATE PROCEDURE GetFilteredLogs
    @FromDate       datetime2,
    @ToDate         datetime2,
    @SearchText     nvarchar(100) = NULL,
    @LogTypeIds     Ids READONLY,
    @AreaIds        Ids READONLY,
    @SubTypeIds     Ids READONLY,
    @UnitIds        Ids READONLY,
    @SortField      nvarchar(25) = NULL,
    @SortDirection  nvarchar(5) = NULL
AS

SELECT *
FROM LogsView
WHERE   (CreatedDate >= @FromDate AND CreatedDate <= @ToDate) AND 
                (       
                    (
                        ([Text] LIKE '%' + @SearchText + '%' OR @SearchText IS NULL)
                        AND (LogTypeId IN (SELECT Id FROM @LogTypeIds) OR NOT EXISTS (SELECT 1 FROM @LogTypeIds))
                        AND (OperationAreaId IN (SELECT Id FROM @AreaIds) OR NOT EXISTS (SELECT 1 FROM @AreaIds))
                        AND (Subtype IN (SELECT Id FROM @SubTypeIds) OR NOT EXISTS (SELECT 1 FROM @SubTypeIds))
                        AND (Unit IN (SELECT Id FROM @UnitIds) OR NOT EXISTS (SELECT 1 FROM @UnitIds))
                    ) OR IsCritical = 1
                )   
ORDER BY
    CASE @SortField
    WHEN 'LogTypeId' THEN CreatedDate DESC(should be passed as argument), LogTypeId DESC
    ELSE CreatedDate DESC
    END

GO

The order by section in the above sproc is not in correct syntax. How can I do it and is it possible the way I have tried above?

Perhaps you would be better off doing the ordering in the application layer, *not* the SQL layer. — Thom A, Jul 19 '23 at 10:22
Case is an *expression*, you cannot use it like that you would need to have a separate expression for each column. If you must do this in SQL imo you'd be better off dynamically building the query. — Stu, Jul 19 '23 at 10:30
you can use ROW_NUMBER() or (- ROW_NUMBER()) together with your CASE WHENs when you generate the sort, if you really want to avoid dynamic SQL, but it gets pretty ugly pretty quickly, in which case, you should probably look into the Kitching sink answer — siggemannen, Jul 19 '23 at 12:02

score 3 · Accepted Answer · answered Jul 19 '23 at 11:21

This is a classic Kitchen Sink Query.

Use dynamic SQL to build up a series of conditions and ordering clauses. Then use sp_executesql to execute it, passing in all necessary parameters.

CREATE OR ALTER PROCEDURE GetFilteredLogs
    @FromDate       datetime2,
    @ToDate         datetime2,
    @SearchText     nvarchar(100) = NULL,
    @LogTypeIds     Ids READONLY,
    @AreaIds        Ids READONLY,
    @SubTypeIds     Ids READONLY,
    @UnitIds        Ids READONLY,
    @SortField      nvarchar(25) = NULL,
    @SortDirection  nvarchar(5) = NULL
AS

DECLARE @sql nvarchar(max) = '
SELECT *
FROM LogsView
WHERE (CreatedDate >= @FromDate AND CreatedDate <= @ToDate)
  AND (
    IsCritical = 1
    OR (1=1';

IF @SearchText IS NOT NULL
    SET @sql += '
      AND [Text] LIKE ''%'' + @SearchText + ''%''';

IF EXISTS (SELECT 1 FROM @LogTypeIds)
    SET @sql += '
      AND LogTypeId IN (SELECT Id FROM @LogTypeIds)';

IF EXISTS (SELECT 1 FROM @AreaIds)
    SET @sql += '
      AND OperationAreaId IN (SELECT Id FROM @AreaIds)';

IF EXISTS (SELECT 1 FROM @SubTypeIds)
    SET @sql += '
      AND Subtype IN (SELECT Id FROM @SubTypeIds)';

IF EXISTS (SELECT 1 FROM @UnitIds)
    SET @sql += '
      AND Unit IN (SELECT Id FROM @UnitIds)';

SET @sql += '
    )
  )
ORDER BY
  CreatedDate' + IIF(@SortDirection = 'DESC', ' DESC', '');

IF @SortField = 'LogTypeId'
    SET @sql += ',
LogTypeId' + IIF(@SortDirection = 'DESC', ' DESC', '');

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
  N'@FromDate       datetime2,
    @ToDate         datetime2,
    @SearchText     nvarchar(100),
    @LogTypeIds     Ids READONLY,
    @AreaIds        Ids READONLY,
    @SubTypeIds     Ids READONLY,
    @UnitIds        Ids READONLY',

    @FromDate = @FromDate,
    @ToDate = @ToDate,
    @SearchText = @SearchText,
    @LogTypeIds = @LogTypeIds,
    @AreaIds = @AreaIds,
    @SubTypeIds = @SubTypeIds,
    @UnitIds = @UnitIds
;

It's not clear from your description, but it seems you always want to sort by CreatedDate, and possibly also by LogTypeId depending on the parameter.

How to create a dynamic order by query with with multiple fields

1 Answers1

Linked

Related