hi

We can parameterize ORDER BY in the ranking functions ( @someName
holds a value “FirstName )

CREATE PROCEDURE example
(
@someName varchar(16)
)

AS

SELECT
EmployeeId,
FirstName,
ROW_NUMBER() OVER (ORDER BY @someName) AS RowNum
FROM Employees

But for some reason we can’t parameterize ORDER BY clause in the query
(@someName holds a value “FirstName”):


CREATE PROCEDURE example
(
@someName varchar(16)
)

AS

SELECT
EmployeeId,
FirstName
FROM Employees
ORDER BY @someName


The above code gives me the following exception:

“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.”

a) If I understand the above quote, then ORDER BY only allows an
expression to contain a variable?! Could you provide me with a simple
example, since I’m not sure what kind of an expression could be used
in ORDER BY clause?

b) Anyways, why can’t we parameterize the ORDER BY clause?


2) Could you tell me which parts of SQL Select/Update/Insert queries
can be parameterized and which parts can’t be, and why not?

Thank you

klem s wrote:
You do not really parameterize the ORDER BY clause here. Simply it is allowed to use a variable. However, the variable
is treated as constant and completely ignored, so the results of ROW_NUMBER do not follow any particular order. The
following example demonstrates that:

CREATE TABLE Employees (
employee_id INT NOT NULL PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30));

INSERT INTO Employees VALUES(1, 'Joe', 'Doe');
INSERT INTO Employees VALUES(2, 'Andy', 'Green');

DECLARE @sort VARCHAR(30);

SET @sort = 'first_name';

SELECT first_name, last_name, ROW_NUMBER() OVER(ORDER BY @sort) AS rk
FROM Employees;

/*

first_name last_name rk
------------------------------ ------------------------------ --------------------
Joe Doe 1
Andy Green 2

*/

This is for historical reasons (ANSI SQL allows to use variables as expressions). You can still use a parameter/variable:

SELECT
EmployeeId,
FirstName
FROM Employees
ORDER BY (SELECT @someName);

However, in this case the variable is treated the same way it is treated in the OVER clause, as constant. You can see
that with the example I gave you earlier:

SELECT first_name, last_name
FROM Employees
ORDER BY (SELECT @sort);

/*

first_name last_name
------------------------------ ------------------------------
Joe Doe
Andy Green

*/

Here is from SQL Server Books Online:

order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer
representing the position of the name or alias in the select list. An integer cannot be specified when the
order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in
SQL Server (90) compatibility mode, the expression cannot resolve to a constant. Column names and aliases can be
qualified by the table or view name. In SQL Server, qualified column names and aliases are resolved to columns listed in
the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the
SELECT statement.

Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization
of the sorted result set.

The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified,
or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns
must appear in the select list.

Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those
specified in the first select list.

Note:
ntext, text, image, or xml columns cannot be used in an ORDER BY clause.


Answered above.

Object names come to mind (server, database, schema, table and column names).

--
Plamen Ratchev
http://www.SQLStudio.com

"klem s" <sqlsurfring (AT) gmail (DOT) com> wrote

hi

We can parameterize ORDER BY in the ranking functions ( @someName
holds a value “FirstName )

CREATE PROCEDURE example
(
@someName varchar(16)
)

AS

SELECT
EmployeeId,
FirstName,
ROW_NUMBER() OVER (ORDER BY @someName) AS RowNum
FROM Employees

But for some reason we can’t parameterize ORDER BY clause in the query
(@someName holds a value “FirstName”):


CREATE PROCEDURE example
(
@someName varchar(16)
)

AS

SELECT
EmployeeId,
FirstName
FROM Employees
ORDER BY @someName


The above code gives me the following exception:

“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.”

a) If I understand the above quote, then ORDER BY only allows an
expression to contain a variable?! Could you provide me with a simple
example, since I’m not sure what kind of an expression could be used
in ORDER BY clause?

b) Anyways, why can’t we parameterize the ORDER BY clause?


2) Could you tell me which parts of SQL Select/Update/Insert queries
can be parameterized and which parts can’t be, and why not?

Thank you

To add to what Plamen has said...

You can use a CASE statement, e.g.

ROW_NUMBER() OVER (ORDER BY CASE WHEN @someName = 'first_name' THEN
first_name WHEN @someName = 'surname' THEN surname ELSE CAST(Employeeid AS
varchar(16)) END

But as the example shows they have to be the same data type and converting
numerics to character may not give the result you expect. It also can get
unweildy when you use more than one WHEN clause.

John

The first ORDER BY is part of a function; functions have parameters.
The second ORDER BY is part of a cursor; cursors are like files and
have to e defined before they are used.

hi, I’m sorry for so many questions, but I’m still a bit confused
about the whole thing:

On Dec 12, 10:27*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
What exactly do you mean by @sort variable being treated as constant?
What is the value of this constant, since if I replace @sort variable
with the ‘first_name’ constant, I get an exception saying “Windowed
functions do not support constants as ORDER BY clause expressions”:

SELECT first_name, ROW_NUMBER() OVER(ORDER BY 'first_name') AS rk
FROM Employees;

You mean that only due to historical reasons doesn’t a query throw an
exception when variable is part of an expression (inside query’s ORDER
BY clause )?


So in other words, query doesn’t throw an exception ( due to
historical reasons ), but on the other hand this query also ignores
our variable?!

In my case ROW_NUMBER() OVER(ORDER BY @sort) the following expression
did result to a constant (else, I assume, an exception would be
thrown), so I assume I don’t have a database is in SQL Server (90)
compatibility mode?!

But didn’t you say that older standards did allow to use variables in
expressions ( used inside ORDER BY)? Then I would assume SQL SERVER
(90) should allow variables to be used in a sort expression?!





I’m not sure what you mean, since while it’s true that we can use
variables in SELECT/UPDATE/INSERT, WHERE and FROM clauses, but there
they are always treated as constants and thus they can’t be used to
directly specify an object’s names --> thus, the following throws an
exception:

DECLARE @Employees VARCHAR(30);

SET @Employees = 'Employees';

SELECT first_name
FROM @Employees; // exception


thank you guys for helping me out

klem s wrote:
It means it is completely ignored and there is no sort oder for the ranking function.

You cannot use directly a literal in place of the variable, you need a SELECT like:

SELECT first_name, last_name, ROW_NUMBER() OVER(ORDER BY (SELECT 'first_name')) AS rk
FROM Employees;

Correct. There are many things in SQL Server inherited from prior versions and some cannot be changed that easily
because of backward compatibility. Over time some old features are depreciated and then newer constructs can be
implements (where conflict do not exist anymore).

Simply because using a variable in ordering expression does not make sense. It cannot be evaluated at run time, it is
simply treated as constant expression and a constant is ignored in sorting.

The Books online extract I posted was from the ORDER BY clause of a query, not from the ranking functions. These are
different and you misinterpret it here. You do get an error as indicated when you use a variable in the query ORDER BY.

No, I did say the SQL standard allows that, not older standards. Again, variables do not make sense in ordering
expressions.

I meant that object names cannot be used as variables.

--
Plamen Ratchev
http://www.SQLStudio.com

"klem s" <sqlsurfring (AT) gmail (DOT) com> wrote

hi, I’m sorry for so many questions, but I’m still a bit confused
about the whole thing:

On Dec 12, 10:27 pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
What exactly do you mean by @sort variable being treated as constant?
What is the value of this constant, since if I replace @sort variable
with the ‘first_name’ constant, I get an exception saying “Windowed
functions do not support constants as ORDER BY clause expressions”:

SELECT first_name, ROW_NUMBER() OVER(ORDER BY 'first_name') AS rk
FROM Employees;


You mean that only due to historical reasons doesn’t a query throw an
exception when variable is part of an expression (inside query’s ORDER
BY clause )?


So in other words, query doesn’t throw an exception ( due to
historical reasons ), but on the other hand this query also ignores
our variable?!

In my case ROW_NUMBER() OVER(ORDER BY @sort) the following expression
did result to a constant (else, I assume, an exception would be
thrown), so I assume I don’t have a database is in SQL Server (90)
compatibility mode?!

But didn’t you say that older standards did allow to use variables in
expressions ( used inside ORDER BY)? Then I would assume SQL SERVER
(90) should allow variables to be used in a sort expression?!





I’m not sure what you mean, since while it’s true that we can use
variables in SELECT/UPDATE/INSERT, WHERE and FROM clauses, but there
they are always treated as constants and thus they can’t be used to
directly specify an object’s names --> thus, the following throws an
exception:

DECLARE @Employees VARCHAR(30);

SET @Employees = 'Employees';

SELECT first_name
FROM @Employees; // exception


thank you guys for helping me out

This is SQL 2008 and 2005

SELECT first_name, ROW_NUMBER() OVER(ORDER BY 'first_name') AS rk
FROM ( SELECT 'John' AS First_name
UNION ALL SELECT 'Paul'
UNION ALL SELECT 'George'
UNION ALL SELECT 'Ringo' ) AS Employees
ORDER BY rk ;
/*
Msg 5309, Level 16, State 1, Line 1
Windowed functions do not support constants as ORDER BY clause expressions.
*/

DECLARE @sort varchar(16)
SET @sort = 'first_name'
SELECT first_name, ROW_NUMBER() OVER(ORDER BY @sort) AS rk
FROM ( SELECT 'John' AS First_name
UNION ALL SELECT 'Paul'
UNION ALL SELECT 'George'
UNION ALL SELECT 'Ringo' ) AS Employees
ORDER BY rk ;
/*
first_name rk
---------- --------------------
John 1
Paul 2
George 3
Ringo 4
*/
-- What you wanted:

SELECT first_name, ROW_NUMBER() OVER(ORDER BY first_name) AS rk
FROM ( SELECT 'John' AS First_name
UNION ALL SELECT 'Paul'
UNION ALL SELECT 'George'
UNION ALL SELECT 'Ringo' ) AS Employees
ORDER BY rk ;
/*
first_name rk
---------- --------------------
George 1
John 2
Paul 3
Ringo 4
*/

-- What I suggested
DECLARE @sort varchar(16)
SET @sort = 'first_name'
SELECT first_name, ROW_NUMBER() OVER(ORDER BY CASE WHEN @sort = 'first_name'
THEN first_name END) AS rk
FROM ( SELECT 'John' AS First_name
UNION ALL SELECT 'Paul'
UNION ALL SELECT 'George'
UNION ALL SELECT 'Ringo' ) AS Employees
ORDER BY rk ;
/*
first_name rk
---------- --------------------
George 1
John 2
Paul 3
Ringo 4
*/
DECLARE @sort varchar(16)
SET @sort = ''
SELECT first_name, ROW_NUMBER() OVER(ORDER BY CASE WHEN @sort = 'first_name'
THEN first_name END) AS rk
FROM ( SELECT 'John' AS First_name
UNION ALL SELECT 'Paul'
UNION ALL SELECT 'George'
UNION ALL SELECT 'Ringo' ) AS Employees
ORDER BY rk ;
/*
Results for 2005
first_name rk
---------- --------------------
John 1
Paul 2
George 3
Ringo 4

Results for 2008
first_name rk
---------- --------------------
Paul 1
George 2
Ringo 3
John 4

*/

I hope that makes things clearer?

John

John,

The parameter @someName contains a scalar value. It does not contain a
column reference (or any other reference). So for each and every row
that is evaluated, the value of @someName will be the same, and thus it
is not a discriminating factor that can influence the ordering.

Then I guess you found an inconsistency in the parser, because in
essence, these evaluate to exactly the same. It would be better if the
parser disallowed both.


The "item ... number 1" refers to the first item that follows the text
ORDER BY.
What this message says, is that is okay to use a variable in an
expression, as long as the expression also references a column. It also
tells that you cannot use a variable to specify a column position. It
says that, because it is still allowed to specify a column position with
a constant.

Valid example with a constant:

SELECT EmployeeId, FirstName FROM Employees ORDER BY 1

This statement would sort the results on EmployeeId, since EmployeeId is
occupies the 1st column position.

Valid example with a variable:

SELECT EmployeeId, FirstName FROM Employees ORDER BY ABS( EmployeeId -
@offset)

So here, the variable @offset is used in the expression
ABS(EmployeeId-@offset). The evaluated result of this expression is used
to sort the results.

In your example, the expression consists of only the variable. Since the
variable will contain one and the same value throughout the entire query
execution (for each evaluated row), sorting on that would sort nothing.


Yes, it "ignores" it. You are trying to assign some magic properties to
the variable that are simply not there. Changing the value of a variable
does not change the purpose or meaning of the query.

Theoretically, in your example, it will retrieve the table rows, and
build a virtual table with the columns EmployeeId, FirstName and
'first_name'. So if your table has three rows, this virtual table might
look like this.

EmployeeId FirstName @someName
---------- --------- ---------
1 John first_name
2 Mary first_name
3 Gert-Jan first_name

Then it will sort the results on 3rd column, (@someName) which will
change nothing in the order of the rows.


Only expressions.

Any other type of parameterization would alter the purpose and meaning
of the query, which would be a mortal sin for SQL, because then you
would be programming in SQL statement. SQL is not about programming. SQL
is about specifying a result. The computer (compiler) will then figure
out the best possible way to determine this result. In short, that is
simply the way SQL works, and SQL Server conforms to this behavior SQL.

--
Gert-Jan
SQL Server MVP

"Gert-Jan Strik" <sorrytoomuchspamalready (AT) xs4all (DOT) nl> wrote

Klem is the OP