procedimientos - procedimiento almacenado busqueda sql server

Clasificación dinámica dentro de procedimientos almacenados de SQL (14)

Este es un problema que he pasado horas investigando en el pasado. Me parece que es algo que deberían haber abordado las soluciones RDBMS modernas, pero hasta ahora no he encontrado nada que realmente aborde lo que considero una necesidad increíblemente común en cualquier aplicación Web o Windows con un back-end de base de datos.

Hablo de clasificación dinámica. En mi mundo de fantasía, debería ser tan simple como algo como:

ORDER BY @sortCol1, @sortCol2

Este es el ejemplo canónico dado por principiantes SQL y desarrolladores de procedimientos almacenados en foros en Internet. "¿Por qué no es esto posible?" ellos preguntan. Invariablemente, alguien finalmente viene a darles una conferencia sobre la naturaleza compilada de los procedimientos almacenados, de los planes de ejecución en general, y todo tipo de otras razones por las cuales no es posible poner un parámetro directamente en una cláusula ORDER BY .

Sé lo que algunos de ustedes ya están pensando: "Deje que el cliente haga la clasificación, entonces". Naturalmente, esto descarga el trabajo de su base de datos. Sin embargo, en nuestro caso, nuestros servidores de bases de datos ni siquiera están empezando a sudar el 99% del tiempo y ni siquiera son multinúcleo o cualquiera de las otras innumerables mejoras a la arquitectura del sistema que ocurren cada 6 meses. Solo por esta razón, hacer que nuestras bases de datos manejen la clasificación no sería un problema. Además, las bases de datos son muy buenas para ordenar. Están optimizados para ello y han tenido años para hacerlo bien, el lenguaje para hacerlo es increíblemente flexible, intuitivo y simple y, sobre todo, cualquier escritor principiante de SQL sabe cómo hacerlo y, lo que es más importante, saben cómo editarlo. hacer cambios, hacer mantenimiento, etc. Cuando sus bases de datos están lejos de ser gravadas y usted simplemente quiere simplificar (y acortar) el tiempo de desarrollo, esto parece una opción obvia.

Luego está el problema de la web. He jugado con JavaScript que hará la clasificación de tablas HTML del lado del cliente, pero inevitablemente no son lo suficientemente flexibles para mis necesidades y, una vez más, dado que mis bases de datos no son excesivamente exigentes y puedo hacer la clasificación de manera realmente fácil, tener dificultades para justificar el tiempo que tomaría volver a escribir o rodar mi propio clasificador de JavaScript. Lo mismo ocurre en general con la clasificación del lado del servidor, aunque ya es probablemente preferible a JavaScript. No soy uno que le guste especialmente la sobrecarga de DataSets, así que denígame.

Pero esto trae de vuelta el punto de que no es posible, o mejor dicho, no es fácil. He hecho, con sistemas anteriores, una forma increíble de hackear la clasificación dinámica. No era bonito, ni intuitivo, simple o flexible, y un escritor de SQL principiante se perdería en cuestión de segundos. Ya esto no es tanto una "solución" sino una "complicación".

Los siguientes ejemplos no están destinados a exponer ningún tipo de mejores prácticas o un buen estilo de codificación ni nada, ni son indicativos de mis habilidades como programador de T-SQL. Son lo que son y reconozco que son confusos, de mala forma y simplemente piratean.

Pasamos un valor entero como un parámetro a un procedimiento almacenado (llamemos al parámetro simplemente "ordenar") y de eso determinamos un grupo de otras variables. Por ejemplo ... digamos que ordenar es 1 (o el valor predeterminado):

DECLARE @sortCol1 AS varchar(20) DECLARE @sortCol2 AS varchar(20) DECLARE @dir1 AS varchar(20) DECLARE @dir2 AS varchar(20) DECLARE @col1 AS varchar(20) DECLARE @col2 AS varchar(20) SET @col1 = ''storagedatetime''; SET @col2 = ''vehicleid''; IF @sort = 1 -- Default sort. BEGIN SET @sortCol1 = @col1; SET @dir1 = ''asc''; SET @sortCol2 = @col2; SET @dir2 = ''asc''; END ELSE IF @sort = 2 -- Reversed order default sort. BEGIN SET @sortCol1 = @col1; SET @dir1 = ''desc''; SET @sortCol2 = @col2; SET @dir2 = ''desc''; END

Ya se puede ver cómo si declarase más variables de @colX para definir otras columnas, podría ser realmente creativo con las columnas para ordenar en función del valor de "ordenar" ... para usarlo, por lo general, termina luciendo como el siguiente cláusula increíblemente desordenada:

ORDER BY CASE @dir1 WHEN ''desc'' THEN CASE @sortCol1 WHEN @col1 THEN [storagedatetime] WHEN @col2 THEN [vehicleid] END END DESC, CASE @dir1 WHEN ''asc'' THEN CASE @sortCol1 WHEN @col1 THEN [storagedatetime] WHEN @col2 THEN [vehicleid] END END, CASE @dir2 WHEN ''desc'' THEN CASE @sortCol2 WHEN @col1 THEN [storagedatetime] WHEN @col2 THEN [vehicleid] END END DESC, CASE @dir2 WHEN ''asc'' THEN CASE @sortCol2 WHEN @col1 THEN [storagedatetime] WHEN @col2 THEN [vehicleid] END END

Obviamente, este es un ejemplo muy despojado. Las cosas reales, ya que usualmente tenemos cuatro o cinco columnas para apoyar la clasificación, cada una con una posible segunda columna secundaria o incluso una tercera para ordenar además de eso (por ejemplo, fecha descendente, luego ordenadas de forma secundaria por nombre ascendente) y cada una de clasificación direccional que efectivamente duplica el número de casos. Sí ... se pone peludo muy rápido.

La idea es que uno pueda "fácilmente" cambiar los casos de clasificación de modo que vehicleid sea ordenado antes de la fecha de almacenamiento ... pero la pseudo-flexibilidad, al menos en este ejemplo simple, realmente termina ahí. Esencialmente, cada caso que falla una prueba (porque nuestro método de clasificación no se aplica a esta vez) representa un valor NULO. Y así terminas con una cláusula que funciona de la siguiente manera:

ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah

Entiendes la idea. Funciona porque SQL Server efectivamente ignora valores nulos en orden por cláusulas. Esto es increíblemente difícil de mantener, ya que cualquiera con conocimientos básicos de SQL puede ver. Si he perdido a alguno de ustedes, no se sientan mal. Nos llevó mucho tiempo hacerlo funcionar y aún nos confundimos al tratar de editarlo o crear otros nuevos como este. Afortunadamente, no es necesario cambiar a menudo, de lo contrario, rápidamente "no valdría la pena".

Sin embargo , funcionó.

Mi pregunta es: ¿hay una mejor manera?

Estoy de acuerdo con las soluciones que no sean procedimientos almacenados, ya que me doy cuenta de que puede no ser el camino a seguir. Preferiblemente, me gustaría saber si alguien puede hacerlo mejor dentro del Procedimiento almacenado, pero si no, ¿cómo se las arregla para que el usuario pueda ordenar dinámicamente las tablas de datos (bidireccionalmente también) con ASP.NET?

¡Y gracias por leer (o al menos hojear) una pregunta tan larga!

PD: me alegro de no haber mostrado mi ejemplo de un procedimiento almacenado que admite la clasificación dinámica, el filtrado dinámico / búsqueda de texto de columnas, la paginación a través de ROWNUMBER () OVER, y trata ... atrapar con el rollback de transacciones en los errores ... "tamaño gigante" ni siquiera comienza a describirlos.

Actualizar:

Me gustaría evitar SQL dinámico . Analizar una cadena y ejecutar un EXEC en ella derrota el propósito de tener un procedimiento almacenado en primer lugar. A veces me pregunto si los inconvenientes de hacer tal cosa no valdrían la pena, al menos en estos casos especiales de clasificación dinámica. Aún así, siempre me siento sucio cada vez que hago cadenas de SQL dinámicas como esa, como si todavía estuviera viviendo en el mundo ASP clásico.
Una gran parte del motivo por el que queremos los procedimientos almacenados en primer lugar es para la seguridad . No llego a hacer un llamado por cuestiones de seguridad, solo sugiero soluciones. Con SQL Server 2005 podemos establecer permisos (por usuario, si es necesario) a nivel de esquema en procedimientos almacenados individuales y luego negar cualquier consulta directamente a las tablas. Criticar los pros y los contras de este enfoque es tal vez para otra pregunta, pero nuevamente no es mi decisión. Solo soy el mono del código principal. :)

¿Qué tal si manejamos la clasificación de las cosas que muestran los resultados: cuadrículas, informes, etc. en lugar de SQL?

EDITAR:

Para aclarar las cosas desde que se obtuvo esta respuesta, voté antes, elaboraré un poco ...

Usted afirmó que sabía sobre la clasificación del lado del cliente, pero que quería mantenerse al margen. Esa es su llamada, por supuesto.

Lo que quiero señalar, sin embargo, es que al hacerlo desde el lado del cliente, puede extraer datos UNA VEZ y luego trabajar con él de la manera que desee, en lugar de hacer múltiples viajes de ida y vuelta al servidor cada vez. el género se cambia

Su SQL Server no se está gravando en este momento y eso es increíble. No debería ser. Pero el hecho de que no esté sobrecargado no significa que se mantenga así para siempre.

Si está utilizando alguna de las cosas más nuevas de ASP.NET para mostrar en la web, muchas de esas cosas ya están preparadas.

¿Vale la pena agregar tanto código a cada procedimiento almacenado solo para manejar la clasificación? De nuevo, tu llamada.

Yo no soy el que finalmente estará a cargo de apoyarlo. Pero reflexione sobre lo que se verá involucrado a medida que se agregan / eliminan columnas dentro de los diversos conjuntos de datos utilizados por los procedimientos almacenados (que requieren modificaciones a las declaraciones CASE) o cuando de repente, en lugar de clasificar por dos columnas, el usuario decide que necesitan tres: requiriendo que ahora actualice cada uno de sus procedimientos almacenados que usan este método.

Para mí, vale la pena obtener una solución funcional del lado del cliente y aplicarla a las pocas pantallas de datos orientadas al usuario y terminar con eso. Si se agrega una nueva columna, ya está procesada. Si el usuario desea ordenar por varias columnas, puede ordenar por dos o veinte de ellas.

Cuando busca resultados ordenados, SQL dinámico es una buena opción. Si está paranoico con la inyección de SQL, puede usar los números de columna en lugar del nombre de la columna. He hecho esto antes de usar valores negativos para descender. Algo como esto...

declare @o int; set @o = -1; declare @sql nvarchar(2000); set @sql = N''select * from table order by '' + cast(abs(@o) as varchar) + case when @o < 0 then '' desc'' else '' asc'' end + '';'' exec sp_executesql @sql

Entonces solo necesita asegurarse de que el número esté dentro de 1 a # de columnas. Incluso podría expandir esto a una lista de números de columna y analizar eso en una tabla de entradas usando una función como this . Entonces construirías la orden por cláusula así ...

declare @cols varchar(100); set @cols = ''1 -2 3 6''; declare @order_by varchar(200) select @order_by = isnull(@order_by + '', '', '''') + cast(abs(number) as varchar) + case when number < 0 then '' desc'' else '''' end from dbo.iter_intlist_to_tbl(@cols) order by listpos print @order_by

Una desventaja es que debe recordar el orden de cada columna en el lado del cliente. Especialmente, cuando no muestra todas las columnas o las visualiza en un orden diferente. Cuando el cliente desea ordenar, asigna los nombres de las columnas al orden de las columnas y genera la lista de entradas.

Debe evitar la clasificación de SQL Server, a menos que sea necesario. ¿Por qué no ordenar en el servidor de la aplicación o el lado del cliente? También .NET Generics hace una excepción excepcional

En algún momento, ¿no vale la pena alejarse de los procedimientos almacenados y simplemente utilizar consultas parametrizadas para evitar este tipo de hackers?

Esta solución solo podría funcionar en .NET, no lo sé.

Recojo los datos en el C # con el orden de clasificación inicial en la cláusula de orden SQL, coloco esos datos en un DataView, los guardo en una variable de sesión y los utilizo para construir una página.

Cuando el usuario hace clic en el encabezado de una columna para ordenar (o página, o filtrar), no vuelvo a la base de datos. En su lugar, vuelvo a mi DataView almacenado en caché y configuro su propiedad "Ordenar" a una expresión que construyo dinámicamente, tal como lo haría con el SQL dinámico. (Hago el filtrado de la misma manera, usando la propiedad "RowFilter").

Puedes verlo / sentirlo trabajando en una demostración de mi aplicación, BugTracker.NET, en http://ifdefined.com/btnet/bugs.aspx

Este enfoque evita que las columnas clasificables se dupliquen dos veces en el orden por, y es una OMI un poco más legible:

SELECT s.* FROM (SELECT CASE @SortCol1 WHEN ''Foo'' THEN t.Foo WHEN ''Bar'' THEN t.Bar ELSE null END as SortCol1, CASE @SortCol2 WHEN ''Foo'' THEN t.Foo WHEN ''Bar'' THEN t.Bar ELSE null END as SortCol2, t.* FROM MyTable t) as s ORDER BY CASE WHEN @dir1 = ''ASC'' THEN SortCol1 END ASC, CASE WHEN @dir1 = ''DESC'' THEN SortCol1 END DESC, CASE WHEN @dir2 = ''ASC'' THEN SortCol2 END ASC, CASE WHEN @dir2 = ''DESC'' THEN SortCol2 END DESC

Estoy de acuerdo, use el lado del cliente. Pero parece que esa no es la respuesta que quieres escuchar.

Entonces, es perfecto como es. No sé por qué querrías cambiarlo, o incluso preguntar "¿Hay una mejor manera?" Realmente, debería llamarse "El Camino". Además, parece funcionar y se adapta perfectamente a las necesidades del proyecto y probablemente sea extensible en los próximos años. Dado que sus bases de datos no están sujetas a impuestos y la clasificación es realmente muy fácil , debería seguir así en los años venideros.

No lo sudaría.

Hay un par de formas diferentes de hackear esto.

Prerrequisitos:

Solo una instrucción SELECT en el sp
Dejar de lado cualquier clasificación (o tener un valor predeterminado)

Luego inserte en una tabla temporal:

create table #temp ( your columns ) insert #temp exec foobar select * from #temp order by whatever

Método n. ° 2: configure un servidor vinculado de nuevo consigo mismo, luego seleccione de esto usando openquery: http://www.sommarskog.se/share_data.html#OPENQUERY

Mis aplicaciones hacen esto mucho, pero todas están construyendo dinámicamente el SQL. Sin embargo, cuando trato con procedimientos almacenados hago esto:

Convierta el procedimiento almacenado en una función que devuelva una tabla de sus valores, sin ordenar.
Luego, en el código de su aplicación, select * from dbo.fn_myData() where ... order by ... para que pueda especificar dinámicamente el orden de clasificación allí.

Entonces, al menos, la parte dinámica está en su aplicación, pero la base de datos todavía está haciendo un gran trabajo.

Puede haber una tercera opción, ya que su servidor tiene muchos ciclos de repuesto: use un procedimiento de ayuda para hacer la clasificación a través de una tabla temporal. Algo como

create procedure uspCallAndSort ( @sql varchar(2048), --exec dbo.uspSomeProcedure arg1,''arg2'',etc. @sortClause varchar(512) --comma-delimited field list ) AS insert into #tmp EXEC(@sql) declare @msql varchar(3000) set @msql = ''select * from #tmp order by '' + @sortClause EXEC(@msql) drop table #tmp GO

Advertencia: no he probado esto, pero "debería" funcionar en SQL Server 2005 (que creará una tabla temporal a partir de un conjunto de resultados sin especificar las columnas de antemano).

Sí, es un dolor, y la forma en que lo haces es similar a lo que hago:

order by case when @SortExpr = ''CustomerName'' and @SortDir = ''ASC'' then CustomerName end asc, case when @SortExpr = ''CustomerName'' and @SortDir = ''DESC'' then CustomerName end desc, ...

Esto, para mí, es mucho mejor que crear código SQL dinámico a partir de código, lo que se convierte en una pesadilla de escalabilidad y mantenimiento para los DBA.

Lo que hago desde el código es refactorizar la paginación y la ordenación, así que al menos no tengo mucha repetición allí con los valores @SortExpr para @SortExpr y @SortDir .

En lo que respecta al SQL, mantenga el diseño y el formato de la misma forma entre los diferentes procedimientos almacenados, de modo que al menos sea claro y reconocible cuando ingrese para realizar cambios.

SQL dinámico sigue siendo una opción. Solo tienes que decidir si esa opción es más aceptable de lo que tienes actualmente.

Aquí hay un artículo que muestra que: http://www.4guysfromrolla.com/webtech/010704-1.shtml .

Un argumento en contra de hacer la clasificación en el lado del cliente es información de gran volumen y paginación. Una vez que el recuento de filas va más allá de lo que puede visualizar fácilmente, a menudo se clasifica como parte de un salto / toma, que probablemente desee ejecutar en SQL.

Para Entity Framework, puede usar un procedimiento almacenado para manejar su búsqueda de texto. Si encuentra el mismo problema de ordenación, la solución que he visto es utilizar un proceso almacenado para la búsqueda, devolviendo solo una clave de identificación establecida para la coincidencia. A continuación, vuelva a consultar (con el género) en contra de la base de datos usando los identificadores en una lista (contiene). EF maneja esto bastante bien, incluso cuando el conjunto de ID es bastante grande. Sí, se trata de dos viajes redondos, pero le permite mantener siempre su clasificación en la base de datos, lo que puede ser importante en algunas situaciones y le impide escribir una gran cantidad de lógica en el procedimiento almacenado.

Una técnica de procedimiento almacenado (¿hackeo?) Que solía evitar el SQL dinámico para ciertos trabajos es tener una columna de clasificación única. Es decir,

SELECT name_last, name_first, CASE @sortCol WHEN ''name_last'' THEN [name_last] ELSE 0 END as mySort FROM table ORDER BY mySort

Este es fácil de superar: puede concaturar campos en su columna mySort, invertir el orden con funciones matemáticas o de fecha, etc.

Sin embargo, preferiblemente, utilizo mi asp.net gridviews u otros objetos con la clasificación incorporada para hacer la clasificación para mí DESPUÉS de recuperar los datos del servidor Sql. O incluso si no está incorporado, por ejemplo, tablas de datos, etc. en asp.net.