A constant expression was encountered in the ORDER BY list

Below is my dynamic query and it’s not working.

It threw:

A constant expression was encountered in the ORDER BY list, position 2.

Original dynamic query:

SET @QueryVendorName = ';WITH 
cteForPriceVen AS (select AI.ItemID, AI.ItemPartNumber as ItemPartNumber, AI.ItemDescription, cteForPrice.VendorPrice as Price, cteForPrice.UpdatedDate as UpdatedDate, cteForPrice.IsLocked as IsLocked
from AerospaceItems (nolock) AI
inner join VendorItemPricing (nolock) cteForPrice
on AI.ItemPartNumber = cteForPrice.ItemPartNumber where cteForPrice.VendorName = ''' + @VendorName + ''' AND
cteForPrice.ObsoleteItem = ''' + cast (@ItemType as char(1)) + ''') select * from cteForPriceVen'

SET @OrderQuery = '
    WHERE (''' + cast (@Description as varchar(250)) + ''' = '''' OR cteForPriceVen.ItemDescription like ''%' + cast (@Description as varchar(250)) + '%'')
    AND (''' + cast (@PartNumber as varchar(99)) + ''' = '''' OR cteForPriceVen.ItemPartNumber like ''%' + cast (@PartNumber as varchar(99)) + '%'')
    AND (''' + cast (@PriceFrom as varchar(25)) + ''' = '''' OR Price >= ''' + cast (@PriceFrom as varchar(99)) + ''')
    AND (''' + cast (@PriceTo as varchar(25)) + ''' = '''' OR Price <= ''' + cast (@PriceTo as varchar(99)) + ''')
    AND (''' + cast (@DateFrom as varchar(25)) + ''' = '''' OR UpdatedDate >= ''' + cast (@DateFrom as varchar(99)) + ''')
    AND (''' + cast (@DateTo as varchar(25)) + ''' = '''' OR UpdatedDate <= ''' + cast (@DateTo as varchar(99)) + ''')

    ORDER BY
    CASE WHEN '''+ @OrderBy +'''=''ItemDescription'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN cteForPriceVen.ItemDescription END ASC,
    CASE WHEN '''+ @OrderBy +'''=''ItemDescription'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN cteForPriceVen.ItemDescription END DESC,
    CASE WHEN '''+ @OrderBy +'''=''ItemPartNumber'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN cteForPriceVen.ItemPartNumber END ASC,
    CASE WHEN '''+ @OrderBy +'''=''ItemPartNumber'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN cteForPriceVen.ItemPartNumber END DESC,
    CASE WHEN '''+ @OrderBy +'''=''Price'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN Price END ASC,
    CASE WHEN '''+ @OrderBy +'''=''Price'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN Price END DESC,
    CASE WHEN '''+ @OrderBy +'''=''UpdatedDate'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''0'' THEN UpdatedDate END ASC,
    CASE WHEN '''+ @OrderBy +'''=''UpdatedDate'' AND '''+ cast (@OrderMode as varchar(10)) +'''= ''1'' THEN UpdatedDate END DESC'

Extracted Query:

;WITH 
cteForPriceVen AS (select AI.ItemID, AI.ItemPartNumber as ItemPartNumber, AI.ItemDescription, cteForPrice.VendorPrice as Price, cteForPrice.UpdatedDate as UpdatedDate, cteForPrice.IsLocked as IsLocked
from AerospaceItems (nolock) AI
inner join VendorItemPricing (nolock) cteForPrice
on AI.ItemPartNumber = cteForPrice.ItemPartNumber where cteForPrice.VendorName = 'Apple' AND
cteForPrice.ObsoleteItem = '0') select * from cteForPriceVen
    WHERE ('' = '' OR cteForPriceVen.ItemDescription like '%%')
    AND ('' = '' OR cteForPriceVen.ItemPartNumber like '%%')
    AND ('' = '' OR Price >= '')
    AND ('' = '' OR Price <= '')
    AND ('' = '' OR UpdatedDate >= '')
    AND ('' = '' OR UpdatedDate <= '')

    ORDER BY
    CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '0' THEN cteForPriceVen.ItemDescription END ASC,
    CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC

If I remove second order by line, CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC the query seems working.

Answer

The second line:

CASE WHEN 'ItemDescription'='ItemDescription' AND '0'= '1' THEN cteForPriceVen.ItemDescription END DESC

Is equivalent to NULL. You can’t order something by NULL.

edit

If this statement is being generated by a dynamic query, what you need to do is fix the way you build the dynamic query:

@orderQuery = ' ORDER BY '
IF @OrderBy = 'ItemDescription'
BEGIN
  orderQuery += ' cteForPriceVen.ItemDescription '
  orderQuery += CASE WHEN @OrderMode = 1 THEN 'ASC' ELSE 'DESC' END
END

Advertisement

Answer

edit