+ Reply to Thread
Results 1 to 7 of 7

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

  1. What parts of a SQL query can be parameterized and what parts can't?

    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

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


    "klem s" wrote in message
    news:e5872c71-067b-45d0-a1c6-8b6eb27017beate27g2000yqddotgooglegroups.com...
    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


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

    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.

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

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


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


    "klem s" wrote in message
    news:8df811f9-7cbb-4a31-8183-910505a46f94atj4g2000yqedotgooglegroups.com...
    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 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


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

    John,

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


    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.

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


    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.


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


    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.


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


    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.


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


    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

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


    "Gert-Jan Strik" wrote in message
    news:4B294E23.B2D112BBatxs4alldotnl...
    > John,
    >


    Klem is the OP

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

    >
    > 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.
    >
    >> 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":

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

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

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

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



+ Reply to Thread