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?
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
0
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
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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.
0
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/
0
Vikas GargBusiness Intelligence DeveloperCommented:
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
1
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
0
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

0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.