Ordinamento dinamico all’interno di stored procedure SQL

Questo è un problema che ho trascorso ore a fare ricerche in passato. Mi sembra di essere qualcosa che avrebbe dovuto essere affrontato dalle moderne soluzioni RDBMS , ma fino ad ora non ho trovato nulla che risponda veramente a ciò che vedo essere un’esigenza incredibilmente comune in qualsiasi applicazione Web o Windows con un back-end di database.

Parlo di ordinamento dinamico. Nel mio mondo fantastico, dovrebbe essere semplice come qualcosa del tipo:

ORDER BY @sortCol1, @sortCol2 

Questo è l’esempio canonico fornito dagli sviluppatori newbie di SQL e Stored Procedure in tutto i forum su Internet. “Perché non è ansible?” loro chiedono. Invariabilmente, qualcuno alla fine arriva a dar loro lezioni sulla natura compilata delle stored procedure, dei piani di esecuzione in generale e di tutti i tipi di altri motivi per cui non è ansible inserire un parametro direttamente in una clausola ORDER BY .


So cosa alcuni di voi stanno già pensando: “Lascia che sia il cliente a fare lo smistamento, allora”. Naturalmente, questo scarica il lavoro dal tuo database. Nel nostro caso, tuttavia, i nostri server di database non si rompono nemmeno il 99% delle volte e non sono nemmeno ancora multi-core o nessuno degli altri miglioramenti mirati all’architettura di sistema che si verificano ogni 6 mesi. Solo per questo motivo, avere i nostri database gestire l’ordinamento non sarebbe un problema. Inoltre, i database sono molto bravi nell’ordinamento. Sono ottimizzati per questo e hanno avuto anni per farlo bene, il linguaggio per farlo è incredibilmente flessibile, intuitivo e semplice e, soprattutto, qualsiasi scrittore SQL principiante sa come farlo e ancora più importante sanno come modificarlo, apportare modifiche, fare manutenzione, ecc. Quando i tuoi database sono lungi dall’essere tassati e vuoi solo semplificare (e accorciare!) i tempi di sviluppo, questa sembra una scelta ovvia.

Poi c’è il problema del web. Ho giocato con JavaScript che eseguirà l’ordinamento lato client delle tabelle HTML, ma inevitabilmente non sono abbastanza flessibili per le mie esigenze e, ancora una volta, dato che i miei database non sono eccessivamente tassati e possono fare ordinamento davvero molto facilmente, io avere difficoltà a giustificare il tempo necessario per ri-scrivere o roll-my-own JavaScript sorter. Lo stesso vale generalmente per l’ordinamento lato server, anche se probabilmente è già molto preferito su JavaScript. Non sono uno a cui piace particolarmente il sovraccarico di DataSet, quindi chiamami.

Ma questo riporta al punto che non è ansible – o meglio, non facilmente. Ho fatto, con i sistemi precedenti, un modo incredibilmente efficace per ottenere l’ordinamento dinamico. Non era bello, né intuitivo, semplice o flessibile e uno scrittore SQL principiante sarebbe stato perso in pochi secondi. Questo già sembra non essere tanto una “soluzione” ma una “complicazione”.


I seguenti esempi non intendono esporre alcun tipo di best practice o uno stile di codifica buono o altro, né sono indicativi delle mie capacità come programmatore T-SQL. Sono quello che sono e ammetto pienamente che sono confusi, in ctriggers forma e semplicemente semplici hack.

Passiamo un valore intero come parametro a una stored procedure (chiamiamo il parametro solo “sort”) e da ciò determiniamo un gruppo di altre variabili. Ad esempio … diciamo che sort è 1 (o predefinito):

 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 

Puoi già vedere come ho dichiarato più variabili @colX per definire altre colonne Potrei davvero essere creativo con le colonne per ordinare in base al valore di “sort” … per usarlo, di solito finisce con l’apparire come il seguente clausola incredibilmente disordinata:

 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 

Ovviamente questo è un esempio molto spoglio. La roba vera, dal momento che di solito abbiamo quattro o cinque colonne per supportare l’ordinamento, ognuna con una secondaria o anche una terza colonna da ordinare oltre a quella (ad esempio la data decrescente, quindi ordinata secondariamente per nome crescente) e ogni ordinamento direzionale che raddoppia efficacemente il numero di casi. Sì … diventa peloso davvero veloce.

L’idea è che si possano “facilmente” cambiare i casi di ordinamento in modo tale che il veicolo sia ordinato prima del tempo di archiviazione … ma la pseudo-flessibilità, almeno in questo semplice esempio, finisce davvero lì. Essenzialmente, ogni caso che fallisce un test (perché il nostro metodo di ordinamento non si applica a questa volta) restituisce un valore NULL. E così si finisce con una clausola che funziona come la seguente:

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

Hai un’idea. Funziona perché SQL Server ignora efficacemente i valori null in ordine di clausole. Questo è incredibilmente difficile da mantenere, come probabilmente chiunque può vedere con una conoscenza di base di SQL. Se ho perso qualcuno di voi, non sentitevi male. Ci è voluto molto tempo per farlo funzionare e siamo ancora confusi cercando di modificarlo o crearne di nuovi. Per fortuna non ha bisogno di cambiare spesso, altrimenti diventerebbe rapidamente “non vale la pena.”

Eppure ha funzionato.


La mia domanda è allora: c’è un modo migliore?

Sto bene con soluzioni diverse da quelle Stored Procedure, in quanto mi rendo conto che potrebbe non essere la strada da percorrere. Preferibilmente, vorrei sapere se qualcuno può farlo meglio nella Stored Procedure, ma in caso contrario, come gestite tutti lasciando all’utente la possibilità di ordinare dynamicmente tabelle di dati (anche bidirezionalmente) con ASP.NET?

E grazie per aver letto (o almeno scremato) una domanda così lunga!

PS: Sii contento di non aver mostrato il mio esempio di una stored procedure che supporta l’ordinamento dinamico, il filtraggio dinamico / ricerca del testo delle colonne, la paginazione tramite ROWNUMBER () OVER, E provare … catturare con il rollback delle transazioni sugli errori … “Di dimensioni colossali” non inizia nemmeno a descriverli.


Aggiornare:

  • Vorrei evitare l’SQL dinamico . L’analisi di una stringa insieme e l’esecuzione di un EXEC su di esso sconfigge molto lo scopo di avere una stored procedure in primo luogo. A volte mi chiedo però se i vantaggi di fare una cosa del genere non ne varrà la pena, almeno in questi speciali casi dinamici di smistamento. Tuttavia, mi sento sempre sporco ogni volta che faccio stringhe SQL dinamiche del genere, come se vivessi ancora nel mondo ASP classico.
  • Un sacco di motivi per cui vogliamo che le stored procedure siano in primo luogo è la sicurezza . Non riesco a rispondere alle preoccupazioni sulla sicurezza, suggerisco solo soluzioni. Con SQL Server 2005 è ansible impostare le autorizzazioni (su base per utente se necessario) a livello di schema su singole stored procedure e quindi negare direttamente qualsiasi query alle tabelle. Criticare i pro e i contro di questo approccio è forse per un’altra domanda, ma ancora una volta non è una mia decisione. Sono solo la scimmia del codice. 🙂