Dynamic order by clause in stored procedure

SELECT...
FROM ...
ORDER BY
   CASE @SortExpression
      WHEN 'Customer' THEN CustomerID
      WHEN 'JobDate' THEN JobDate
      ...
      ELSE <some field>
   END

Hi,
Thank you for your reply.

Actually my expression is in the @SortExpression parameter.
From the program I am sending it as a parameter to the stored procedure.

If Session("SortExpression") Is Nothing = False Then
                Dim SortExpression As String = Session("SortExpression") & " " & Session("SortDirection")
                prmSortExpression = New SqlParameter("@SortExpression", SortExpression)
                cmd.Parameters.Add(prmSortExpression)
            Else
                Dim SortExpression As String = " JOBDETAILS.CustomerID, JOBDETAILS.JobNo, JOBDETAILS.JobDate"
                prmSortExpression = New SqlParameter("@SortExpression", SortExpression)
                cmd.Parameters.Add(prmSortExpression)
            End If

I would advise against that as it would require dynamic sql to make that work and it opens you up to possible SQL injection.  Pass in the SortExpression and SortDirection as parameters to the stored procedure if possible.  Certainly it can get fairly complex if you are going to have 3 levels of sorting.  If that is the case then sort it client-side instead of server-side.

Otherwise you have to build the query as a string, append your SortExpression (hope there's no malicious code in it), and use sp_executesql to execute the query.

Thank you very much for trying to help me. But I  am not sure if I understood what you are trying to say.

No problem.

Basically you can't create an entire ORDER BY clause as you are intending to do.  In order to accomplish that you would have to build the query as a string then execute it using sp_executesql.  Below is a link that explains it in more detail.  If you aren't careful and don't thoroughly validate what you are passing as the ORDER BY columnlist someone might be able to inject something like "JOBDETAILS.CustomerID; DROP TABLE JOBDETAILS;" which return the values and then promptly permanently drop your JOBDETAILS table from the database.  I'm sure you can see what a potential problem this is.

If you want dynamic sorting then you can accomplish it by passing a string which can be treated pretty much as an enum which is what I did in my example above.  Now, this starts to get much more complex when you want to enforce multiple levels of sorting though it is possible.

My suggestion if you want to have this kind of sorting is to not try and do it in the stored procedure.  Instead just pass back the data and then do the sorting in your client application before it is displayed in a grid or whatever.

Hope that is clearer.


https://msdn.microsoft.com/en-us/library/ms188001.aspx

http://bobby-tables.com/

Hi,

You can create dynamic SQL then you can execute is using
EXEC(@SQL)

set @SQL = 'Select * from JOBDETAILS where JobOrOpportunity=''O''
                  and(' + @OppCreatedBy +' IS NULL OR OppCreatedBy = ' + @OppCreatedBy + ' or PrivateOrPublic=''PB'')
                  and ( ' + @CustomerID + ' IS NULL OR CustomerID = ' + @CustomerID + ')
                  and ( ' +@JobFromDt + ' IS NULL OR JOBDETAILS.JobDate >= '+@JobFromDt+ ')
                  and ( ' + @JobToDt + ' IS NULL OR JOBDETAILS.JobDate <='+ @JobToDt+ ')
                  and (JOBDETAILS.JobStatus <>''LS'')
                  order by ' + @SortExpression

So by this complete dynamic query you can set value in column for orderby and others as well

Hi Vikas,
Thank you for trying to help me.

I am trying to do what you have suggested. But getting error "Must declare the scalar variable "@OppCreatedBy"." But I have declared that parameter.

This is my stored procedure. Please tell me where am I going wrong.

ALTER PROCEDURE [dbo].[spOpportunity]
                         
           @CustomerID          INT=NULL,
         @JobStatus              CHAR(3)=NULL,
         @OppCreatedBy        int=NULL,
         @JobFromDt           nchar(10)=NULL,
         @JobToDt             nchar(10)=NULL,
         @SortExpression      nchar(200)=NULL,  
         @ShowPublic          nchar(1)=NULL,
         @SQL                 nchar(500)=NULL
        

AS

BEGIN

         set @SQL='Select * from JOBDETAILS where JobOrOpportunity="O"
           and(@OppCreatedBy IS NULL OR OppCreatedBy = @OppCreatedBy)
         and (@CustomerID IS NULL OR CustomerID = @CustomerID)
         and (@JobFromDt IS NULL OR JOBDETAILS.JobDate >=@JobFromDt)
         and (@JobToDt IS NULL OR JOBDETAILS.JobDate <=@JobToDt)
         and (JOBDETAILS.JobStatus <>"LS")
         order by ' + @SortExpression
         EXEC(@SQL)
            
End

Hi Brian,

Thank you very much for your help. Actually I liked your idea to do the sorting after getting back the data. I made the changes and it is working fine. But still I just wanted to know how dynamic SQL works, so your clarification really helped.

Thank u again.