Dynamic order by without using dynamic sql ?

Question

I have the following stored procedure which can be sorted ascending and descending on TemplateName,CreatedOn and UploadedBy. The following SP when runs doesnot sort records.if i replace 2,3,4 by columnname, i got an error message "Conversion failed when converting the nvarchar value 'Test Template' to data type int.".Please suggest how to achieve sorting.

CREATE PROCEDURE [dbo].[usp_SEL_GetRenderingTemplate]
(
  @facilityID INT,
  @sortOrder VARCHAR(5),
  @sortExpression VARCHAR(100),
  @errorCode INT OUTPUT 
)
AS 
BEGIN       
    SET NOCOUNT ON ;
    BEGIN TRY   
        SET @sortOrder = CASE @sortOrder
                           WHEN 'Ascending' THEN 'ASC'
                           WHEN 'Descending' THEN 'DESC'
                           ELSE 'ASC'
                         END
        SELECT  TemplateID,
                TemplateName,
                CreatedOn,
                ( [user].LastName + ' ' + [user].FirstName ) AS UploadedBy
        FROM    Templates
                INNER JOIN [user] ON [user].UserID = Templates.CreatedBy
        WHERE   facilityid = @facilityID
        ORDER BY CASE WHEN @sortExpression = 'TemplateName'
                           AND @sortOrder = 'ASC' THEN 2
                      WHEN @sortExpression = 'CreatedOn'
                           AND @sortOrder = 'ASC' THEN 3
                      WHEN @sortExpression = 'UploadedBy'
                           AND @sortOrder = 'ASC' THEN 4
                 END ASC,
                CASE WHEN @sortExpression = 'TemplateName'
                          AND @sortOrder = 'DESC' THEN 2
                     WHEN @sortExpression = 'CreatedOn'
                          AND @sortOrder = 'DESC' THEN 3
                     WHEN @sortExpression = 'UploadedBy'
                          AND @sortOrder = 'DESC' THEN 4
                END DESC
        SET @errorCode = 0


    END TRY
    BEGIN CATCH
        SET @errorCode = -1                            
        DECLARE @errorMsg AS VARCHAR(MAX)  
        DECLARE @utcDate AS DATETIME   
        SET @errorMsg = CAST(ERROR_MESSAGE() AS VARCHAR(MAX))  
        SET @utcDate = CAST(GETUTCDATE() AS DATETIME)  
        EXEC usp_INS_LogException 'usp_SEL_GetFacilityWorkTypeList',
            @errorMsg, @utcDate  
    END CATCH 


END

Answer 1

We perform a similar sort of dynamic ordering in one of our products. The only real different to your code is firstly, we don't use the live join. We create a temporary table, so we can perform paging, and then apply the order. We also use ints for ordering, cuts down on the overhead of string comparison.

It does make your SQL a bit longer, but this approach is definitely faster for the Query Optimiser. Also, and more importantly, I don't think you can mix types in Switch blocks, for ordering, so to follow your original code, you'd have to CONVERT all your data to the same time, which defeats the object :(

So you'd have

DECLARE @temp TABLE(ID int identity(1,1), TemplateID int, TemplateName nvarchar(100), CreatedOn datetime, UploadedBy nvarchar(100))
INSERT INTO @temp(TemplateID, TemplateName, CreatedOn, UploadedBy)
SELECT TemplateID,
       TemplateName,
       CreatedOn,
       ( [user].LastName + ' ' + [user].FirstName ) AS UploadedBy
FROM Templates
  INNER JOIN [user] ON [user].UserID = Templates.CreatedBy
WHERE   facilityid = @facilityID

Then:

IF @SortOrder = 1 --'ASC'
  BEGIN
     IF @sort = 2
       Select *
       From @Temp
       Order by TemplateName ASC
     ELSE IF @sort = 3
       Select *
       From @Temp
       Order By CreatedBy ASC
     -- and so on...
  END

ELSE -- descending
  BEGIN
    -- Ad. Inf.
  END

Delete
  From @Temp
  WHERE ID < @pageStart or ID > @pageStart + @pageSize

Answer 2

The dynamic ordering has to be of the same datatype. Here is an example of something that I use to case order three different datatypes - integer, date (ascending and descending), and string. This may not work for your situation, but at least you can see some techniques for casting to a common datatype.

...

ORDER BY
    Case Parent.RankTypeID
      When 0 Then dbo.Documents.Rank
      When 1 Then Convert(int, dbo.Documents.DateStart, 112)
      When 2 Then (1 - Convert(int, dbo.Documents.DateStart, 112))
      When 3 Then Cast(dbo.Documents.Title as sql_variant)
    End

Note:

112 is a date format of YYYYMMDD - useful for ordering.

Answer 3

The following SP when runs doesnot sort records.if i replace 2,3,4 by columnname,

You shouldn't replace 2, 3, 4 in the ORDER BY clause by the column name. When you call the procedure just pass the column you want to sort by as the 3rd parameter.

EXEC [dbo].[usp_SEL_GetRenderingTemplate] 1,'Ascending','CreatedOn',@vErrorCode

The CASE will then evaluate the query to something like

...ORDER BY 3 DESC

which sorts the query by the 3rd column in the SELECT clause (i.e. CreatedOn)