Paginazione dati direttamente da query con SQL Server

MS SQL Server è un ottimo prodotto, ma secondo me ha una grande mancanza:

manca la possibilità di specificare in una query che si vogliono solo le righe dalle 11 alla 20.

Qualcuno potrebbe dire che basta fare un Select top 20 * from ... e poi saltare le prime 10 righe in visualizzazione: sfortunatamente il numero dopo il TOP non è parametrizzabile, quindi non si può fare passare ad una stored procedure.

Ovviamente ci sono altri mille modi per fare paginazione - salvare la datatable nella session, magari anche nella cache se è una query generica (il comunissimo elenco news), ecc... - ma io dovevo per forza mettere nella stored tutta la logica di paginazione: stavo facendo il modulo MS SQL per uno dei progetti opensource al quale collaboro (Jgossip) e il modulo di accesso ai dati era fatto in maniera tale che, nonostante ci fossero N drivers per gli N database supportati, questi drivers contenevano solo le query da passare al DB, e non anche tutta la logica di estrazione.

In pratica dovevo fare in modo di ottenere con MS SQL quello con MySQL si fa semplicemente aggiundo alla fine della query

LIMIT start, pageSize

Questo è il codice della SP che emula il LIMIT di MySQL:

CREATE PROCEDURE  sp_GET_LOG_ENTRIES_ASC

@intimePar varchar(255),
@endtimePar varchar(255),
@loggerPar varchar(255),
@log_levelPar varchar(32),
@remote_ipPar varchar(16),
@session_idPar varchar(255),
@user_namePar varchar(32),
@start int,
@pageSize int

... click here to open ...

In pratica creo una tabella temporanea con i campi che mi servono come risultato, poi dichiaro un cursore e lo apro sulla query vera da fare al DB e, durante il fetch popolo la tabella temporanea solo se le righe sono nell'intervallo richiesto, e alla fine faccio il select sulla tabella temporanea (che contiene solo le righe richieste)

Spero che qualcun'altro possa trarre giovamento da questa mia pazzia in SP.

powered by IMHO

posted @ venerdì 3 dicembre 2004 17:54

Print

Comments on this entry:

# re: Paginazione dati direttamente da query con SQL Server

Left by Davide Mauri at 03/12/2004 20:40
Gravatar
Argh! I Cursori no!!!!!!!

Prima di proporti una soluzione ti annuncio che in SQL 2005 questo problema è stato risolto, visto che la clausola TOP sarà parametrizzabile ed inoltre ci saranno delle belle funzioni di ranking.

Di modi per risolvere il problema del paging ce ne sono diversi, ANCHE utilizzando la clausola TOP....perchè ti ricordo che tramite EXEC o, meglio, sp_executesql, puoi eseguire del codice SQL creato a runtime e memorizzato in una variabile di tipo varchar().

Nel tuo caso, ti cosiglio proprio l'utilizzo della clausola TOP:

SELECT TOP <rowsPerPage> <fields> FROM
(SELECT TOP <rowsPerPage> <fields> FROM <table> WHERE <sortingColumn> IN (SELECT TOP <rowsPerPage>*<requestedPage> <sortingColumn> FROM <table> ORDER BY <sortingColumn>) ORDER BY <sortingColumn> DESC) as TempTable
ORDER BY TempTable.<sortingColumn>

# re: Paginazione dati direttamente da query con SQL Server

Left by simone at 03/12/2004 23:18
Gravatar
Si, questa soluzione era quella che avevo pensato pure io, ma eseguire un TOP in una stored con un sp_executesql è come non usare la stored, perchè così facendo non si ha il vantaggio dell'avere la query compilata.
Quindi, fino a SQL 2005 quando sarà parametrizzabile il TOP, dovrò rimanere con i cursori :-)

# re: Paginazione dati direttamente da query con SQL Server

Left by Simone Chiaretta at 04/12/2004 17:25
Gravatar
Farò delle prove.
Grazie
Comments have been closed on this topic.
«ottobre»
domlunmarmergiovensab
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789