Good day.

I do to SQL Server the function returning the table. In addition to the other parameters, I pass to the function the field by which you want to sort and the direction of the sort :

/* к примеру @sidx = 'name'; @sord = 'ASC'; */ ... тело запроса ... ORDER BY @sidx @sord

I get "Incorrect syntax near '@sord'". What is actually a mistake?

  • Try a comma between @sidx and @sord . - Anton Mukhin

3 answers 3

A variable in ORDER BY can only be part of an expression. Such construction, for example, will work.

 ... тело запроса ... ORDER BY '['+@sidx+'] '+@sord
  • I apologize, did not notice that the second variable sets the direction ORDER BY '[' + @ sidx + ']' + @ sord - renegator
  • after the second bracket, space - correct in response - renegator
  • in your case, after all the calculations, it will remain ... ORDER BY '[name] ASC' Try to add such a sort to any SELECT for the sake of interest - Nofate ♦
  • I first check all my answers practically. and this one also checked - renegator
  • you do not understand me. if you add ORDER BY '[name] ASC' to absolutely any select, it will work. Even if there is no name column in that table. Because it will carry out exactly what you are asking: sort by the value of the string '[name] ASC' , which will be attached to each line of the data set. - Nofate ♦

You seem to be unaware of the SQL grammar. ASC is the syntactic construction of the language, 'ASC' is just a string. Further the column name should follow, for example name . You substitute the string 'name' . Feel the difference?

There are two ways out, and both come down to generating the query:

  1. You can generate a query with all substitutions in the program code.

  2. You can generate a query with all substitutions in TSQL and continue to pass parameters as you are doing now. Example:

     declare @query varchar(255) set @query = 'select * from mytable order by ' + @sidx + ' ' + @sord execute(@query)
  • Ie first the name of the column, and then ASC , of course. - Nofate ♦
  • You are not well aware of the possibilities specifically for MS SQL Server. Run this simple script: create table #t (name varchar (20)) insert into #t values ​​('Ivanov') insert into #t values ​​(Petrov ') declare @sidx varchar (20), @sord varchar (20) set @ sidx = 'name' set @ sord = 'asc' select * from #t order by '[' + @ sidx + ']' + @ sord drop table #t - renegator
  • one
    You have a bad idea of ​​the possibilities of SQL. Run this simple script: create table #t (name varchar (20)) insert into #t values ​​('Ivanov') insert into #t values ​​(Petrov ') declare @sidx varchar (20), @sord varchar (20) set @ sidx = 'name' set @ sord = 'desc' select * from #t order by '[' + @ sidx + ']' + @ sord drop table #t;) - Nofate ♦
  • Too hard? - renegator
  • so did you do it or not? My own option. in it, if you look closely, I set the direction of DESC - Nofate ♦

The task you are writing about is solved like this: Dynamic Order By

  • Hmm, interesting, now there is no opportunity to try, but I will definitely try. And the variant with DESC / ASC will also be: ... ORDER BY CASE WHEN @OrderBY = ... END CASE WHEN @sord = + '' + ASC THEN 'ASC' WHEN @sord = 'DESC' THEN + '' + DESC END ...? Or how to be right? - Afipsky
  • No, that won't work. You can, if sorting in ascending order, execute one query, if descending - another. - Modus
  • In the sense of two identical requests, but at the end of the first will be ASC and the second DESC? Can you give an example? - Afipsky
  • sqlteam.com/forums/topic.asp?TOPIC_ID=5942 - Modus
  • one
    @Modus, by the way, the idea is not bad, but it is not very convenient for tables with a large number of columns (30 for example) and is not at all convenient if we want to sort on several columns. - Nofate ♦