Dynamic order by clause in stored procedure

Hi Experts,

In my stored procedure I want to make the order by clause dynamic.

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

But I am getting compilation error-

"The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

Any suggestions?

Thanks.
RadhaKrishnaKiJayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
SELECT...
FROM ...
ORDER BY
   CASE @SortExpression
      WHEN 'Customer' THEN CustomerID
      WHEN 'JobDate' THEN JobDate
      ...
      ELSE <some field>
   END
RadhaKrishnaKiJayaAuthor Commented:
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
Brian CroweDatabase AdministratorCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

RadhaKrishnaKiJayaAuthor Commented:
Thank you very much for trying to help me. But I  am not sure if I understood what you are trying to say.
Brian CroweDatabase AdministratorCommented:
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/
Vikas GargAssociate Principal EngineerCommented:
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
RadhaKrishnaKiJayaAuthor Commented:
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
Brian CroweDatabase AdministratorCommented:
Like I said Dynamic SQL should be avoided but here you go...

https://www.brentozar.com/archive/2015/10/the-five-stages-of-dynamic-sql/

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 

SET @SQL='SELECT * FROM JOBDETAILS WHERE JobOrOpportunity = ''O'' AND JobStatus <> ''LS''';

IF @OppCreatedBy IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' AND OppCreatedBy = ' + CAST(@OppCreatedBy AS NVARCHAR);
END

IF @CustomerID IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' AND CustomerID = ' + CAST(@CustomerID AS NVARCHAR);
END

IF @JobFromDt IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' AND JobDate >= ''' + @JobToDt + '''';
END

IF @JobToDt IS NOT NULL
BEGIN
	SET @SQL = @SQL + ' AND JobDate <= ''' + @JobToDt + '''';
END

SET @SQL = @SQL + ' ORDER BY ' + @SortExpression;

-- For trouble-shooting
PRINT @SQL

EXEC(@SQL);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RadhaKrishnaKiJayaAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.