Nos gustaría utilizar un parámetro en la cláusula «Order By» de una consulta o procedimiento almacenado creado con Visual Studio Diseñador de DataSet.

Ejemplo:

  FROM TableName
 WHERE (Forename LIKE '%' + @SearchValue + '%') OR
       (Surname LIKE '%' + @SearchValue + '%') OR
       (@SearchValue = 'ALL')
ORDER BY @OrderByColumn

Se muestra este error:

Variables are only allowed when ordering by an expression referencing 
a column name.
  • ¿por qué no utilizar la interfaz de usuario / de la capa de presentación?
  • Gracias por la respuesta Beth. Puedes dar más detalles sobre el uso de la capa de presentación para hacer esto? Vamos a utilizar un conjunto de inflexible datos en un ASP.Net GridView.
  • Estoy asumiendo que usted está presentando la salida en algún lugar, se puede hacer allí? ¿qué herramienta que están utilizando para su entrada?
  • Visual Studio 2012 con VB.Net y ASP.Net
  • Para un pequeño GridView con sólo 2 columnas hemos creado 2 consultas en el diseñador de DataSet y llamarlo desde el archivo de código subyacente, pero esto será difícil de mantener con el que hacer un mayor DataView con 10 columnas.
  • Si VS nos permitiría usar ORDER BY @OrderByColumn, que se encargará de eliminar una gran cantidad de código adicional.
  • tienes que pasar la @orderByColumn como un parámetro a la cuadrícula / dataview
  • El problema que estamos teniendo es que VS no nos va a permitir usar ORDER BY @OrderByColumn porque va a mostrar el mensaje de error al hacer clic en el botón «Siguiente» de la asistente. Si se nos permite más allá de ese punto, a continuación, podemos realizar la codificación para la manipulación de los parámetros a pasar a él.

InformationsquelleAutor Emad-ud-deen | 2012-12-12

2 Comentarios

  1. 41

    Usted debería ser capaz de hacer algo como esto:

    SELECT *
    FROM
        TableName
    WHERE
        (Forename LIKE '%' + @SearchValue + '%') OR
        (Surname LIKE '%' + @SearchValue + '%') OR
        (@SearchValue = 'ALL')
    ORDER BY 
        CASE @OrderByColumn
        WHEN 1 THEN Forename
        WHEN 2 THEN Surname
        END;
    • Asignar 1 a @OrderByColumn ordenar Forename.
    • Asignar 2 para ordenar en Surname.
    • Etc… puedes ampliar este esquema arbitrario número de columnas.

    Ser cuidadoso sobre el rendimiento, sin embargo. Este tipo de construcciones pueden interferir con el optimizador de consultas de la capacidad de encontrar un plan de ejecución óptimos. Por ejemplo, incluso si Forename está cubierto por índice, consulta puede exigir la completa especie en lugar de simplemente atravesar el índice en orden.

    Si ese es el caso, y no se puede vivir con las consecuencias en el rendimiento, puede ser necesario disponer de una versión independiente de la consulta para cada posible tipo de orden, lo que complica considerablemente las cosas del lado del cliente.

    • Gracias por la ayuda. Parece VS no como parámetros los nombres de columna. Al menos a su manera va a reducir de algunos de codificación. 🙂
    • Eso es lo que hicimos para la 2 columna DataView ya que es sólo 2 columnas. El 20 de columna requeriría un buen montón de codificación, aunque.
    • El diseñador de DataSet me está diciendo que me «Declarar la variable escalar @OrderByColumn». Me puede decir cómo hacerlo en el diseñador?
    • Lo siento, no estoy familiarizado con eso. En SQL Server Management Studio, usted simplemente: DECLARE @OrderByColumn int = 1; y en ADO.NET código tendría que utilizar los parámetros vinculados (SqlParameter de la clase).
    • Tal vez es mejor hacer la consulta como un procedimiento almacenado. Ya que soy nuevo en procedimientos almacenados, se puede mostrar un simple procedimiento almacenado que permitiría la implementación de @OrderByColumn ?
    • Me enteré de cómo hacer los procedimientos almacenados. Funciona bien ahora. Gracias de nuevo por la ayuda con el Caso de la declaración.
    • Esto es tan útil, me tomó 2 horas para encontrarlo, muchas gracias
    • Sospecho que la mayoría nunca coloque ni siquiera cerca de la cantidad de esfuerzo como este para cerrar este grave de Inyección de SQL agujero. Enlace para params pero no para el fin De..
    • Para evitar que el optimizador lío con esto, usted puede agregar OPTION (RECOMPILE) al final de su consulta
    • Buena sugerencia, a pesar de que tiene sus propias implicaciones de rendimiento (en el orden de varios cientos de milisegundos, en mi experiencia). Así que no es aconsejable para las tablas más pequeñas, pero definitivamente vale la pena para los más grandes.

  2. 1

    Sé que estoy entrando en este hilo de forma tardía, pero sólo quiero publicar esto en caso de que alguien tiene un problema similar.

    El problema parece producirse cuando intenta realizar una ORDER BY directamente en el parámetro, debido a que SQL Server espera que proporcione un número (del 1 para el primero, 2 para el segundo, y así sucesivamente…), o un nombre de columna presentados como un identificador (MyField o «Micampo») o una cadena (‘MyField’).

    Por ejemplo:

    DECLARE @ORDERBY AS NVARCHAR(20)
    ;
    
    SELECT @ORDERBY = :Param1 --(Supposing that the user enters 'MyField')
    ;
    
    SELECT TOP 1 *
    FROM MyTable
    ORDER BY @ORDERBY DESC
    ;

    Obtendrá el siguiente error:

    SELECCIONE el elemento identificado por el ORDEN POR el número 1 contiene un
    variable como parte de la expresión de la identificación de una posición de la columna.
    Las Variables sólo se permite cuando el pedido por una expresión de referencia
    un nombre de columna. (SQLSTATE=42000) (1008) (Severidad=16)

    Si escribir manualmente las consultas en cualquiera de los descritos formas (usando un identificador o una cadena), no hay ningún error.

    SELECT TOP 1 *
    FROM MyTable
    ORDER BY MyField DESC
    ;
    
    SELECT TOP 1 *
    FROM MyTable
    ORDER BY "MyField" DESC
    ;
    
    SELECT TOP 1 *
    FROM MyTable
    ORDER BY 'MyField' DESC
    ;

    Así que si usted realiza una CAST() en ese mismo parámetro, su valor se convierte en una cadena, y la consulta se ejecuta correctamente:

    DECLARE @ORDERBY AS NVARCHAR(20)
    ;
    
    SELECT @ORDERBY = :Param1 --(Supposing that the user enters the text 'MyField')
    ;
    
    SELECT TOP 1 *
    FROM MyTable
    ORDER BY CAST(@ORDERBY AS NVARCHAR(20)) DESC
    ;

    En este caso, (de nuevo, suponiendo que el usuario escribió la cadena ‘Micampo» como el valor de :Param1), el real de la consulta que se está ejecutando es:

    SELECT TOP 1 *
    FROM MyTable
    ORDER BY 'MyField' DESC
    ;

    Esta consulta se ejecuta correctamente, sin errores y sin aparente, impacto significativo en el rendimiento, sin la necesidad de enumerar todas las posibles entradas del usuario en un CASE declaración que podría, potencialmente, se extienden a cientos de posibles valores.

    He utilizado esta solución muchas veces en Microsoft SQL Server, desde 2005 hasta el año 2016, sin problemas de ningún tipo.

    Esperemos que esto todavía puede ser útil a alguien.

    • al convertir el parámetro a un tipo NVARCHAR y el uso que de la ORDEN, POR lo que van a comprar por una constante. Debe ser un identificador. Para SQL Server sólo devuelve las filas en orden arbitrario.

Dejar respuesta

Please enter your comment!
Please enter your name here