Re: What parts of a SQL query can be parameterized and what parts can't?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"klem s" <sqlsurfring@xxxxxxxxx> wrote in message news:8df811f9-7cbb-4a31-8183-910505a46f94@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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...@xxxxxxxxxxxxx> wrote:
klem s 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

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.

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;



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

This is for historical reasons (ANSI SQL allows to use variables as expressions).

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 )?


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.

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?!


> 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?

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.

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?!






> 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?

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


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

.

Relevant Pages

  • Re: finding max and min integers - newbie question
    ... > Why not sort the vector and thus you have the min & the max value at once? ... bounds John wants also apply to his "group". ... otherwise the performance of e.g. the calculation of the union will degrade. ...
    (comp.lang.cpp)
  • Re: stored procedure help - xml explicit
    ... John ... well, sort of. ... > UNION ALL ... > FOR XML EXPLICIT ...
    (microsoft.public.sqlserver.programming)
  • Re: Would You Buy a Car from Chrysler?
    ... the union organizers get 50% signatures on the cards, ... Yes - And the workers are thereby denied a secret ballot. ... will get 50% signatures on cards when the employees and the ... Card check legislation isn't going to affect these companies. ...
    (rec.autos.makers.chrysler)
  • Re: OT: more on the war against workers.
    ... Union Facts was trying to slip a multi-million-dollar ad campaign ... employees and their families to be still and take their beating like ... Washington lobbyist Rick Berman and his Center for Union Facts as just ...
    (alt.guitar.amps)
  • OT The case against public sector unionism
    ... As a non-unionized public sector employee who studies organizations ... drew a line when it came to government workers: ... employees manifests nothing less than an intent on their part to ... that the impact of union support appears to be roughly the same as the ...
    (alt.sports.basketball.nba.la-lakers)