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

agyaoht7 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(122)

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?

sql-server

来源：https://stackoverflow.com/questions/76720257/how-to-create-a-dynamic-order-by-query-with-with-multiple-fields

1条答案

按热度按时间

ffvjumwh1#

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.

赞(0）回复(0）举报 2023-08-02

我来回答

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

1条答案

相关问题

热门标签

最新问答