Durante la programmazione capiterà certamente la necessità di ordinare i dati di query SQL in modo crescente o decrescente.

Si pensi, ad esempio, di dover creare un output di lista nominativi. Ordinarli per nome, ne faciliterebbe la lettura.

In SQL è molto semplice effettuare l’ordinamento. Infatti basta aggiungere, in fondo alla stringa SQL, il parametro ORDER BY indicando di seguito il nome del campo con cui ordinare la query.

Questa query SQL:

SELECT * FROM utenti ORDER BY nome

ordina gli utenti per nome in modo crescente.

E’ possibile ordinare anche in modo decrescente utilizzando:

‘Ordino in modo decrescente

SELECT * FROM utenti ORDER BY nome DESC

Una delle potenzialità maggiori del T-SQL è la manipolazione delle variabili all’interno delle nostre procedure, possiamo manipolare, svuotare e riempire le nostre variabili a piacimento, anche all’interno di istruzioni SQL standard:

USE pubs

GO

— Dichiariamo una variabile @cognome

DECLARE @cognome VARCHAR(255)

— Inseriamo un nuovo record nella tabella authors

INSERT INTO authors VALUES (

‘123-45-6789’,

‘Pippo’,

‘Pipponi’,

‘555 123-1234’,

‘Via dei Tigli 12’,

‘……….’,

‘CA’,

‘12345’,

1 )

–Visualizziamo il cognome appena inserito

SELECT au_fname as [Cognome inserito] FROM authors WHERE au_id = ‘123-45-6789’

–Ora aggiorniamo il cognome con Plutoni invece di Pipponi

UPDATE authors SET @cognome = au_fname = ‘Plutoni’ WHERE au_id = ‘123-45-6789’

— Questo è il contenuto della variabile cognome: @cognome = au_fname = ‘Plutoni’

SELECT au_fname as [Cognome aggiornato] FROM authors WHERE au_id = ‘123-45-6789’

— Adesso cancelliamo il record che abbiamo appena updatato

DELETE FROM authors WHERE au_fname = @cognome

— ora nella tabella authors il record è tato eliminato ma….

SELECT au_fname as [Cognome aggiornato] FROM authors WHERE au_id = ‘123-45-6789’

— Ecco il contenuto della varibile @cognome, anche se il record è stato eliminato

SELECT @cognome as [Contenuto della variabile @cognome]

Dalla versione 7 di SQL Server è possibile usare una istruzione per alterare una stored procedure, ALTER, molto utile per apportare modifiche alle nostre procedure senza doverle ricompilare nuovamente:

USE TEMPDB

GO

ALTER PROCEDURE SP__TEST AS

SELECT * FROM MANAGEMENT WHERE ID > 0

/*

Ovviamente in questo esempio la procedura è inventata

*/

L’esempio ci illustra una grande potenzialità di SQL Server, poter chiamare e gestire chiamate ad oggetti COM presenti sul sistema operativo. Nel nostro caso se avete registrato il componente CDONTS per spedire email ( di default su NT4 Server, no per Windows 98, ME o W2000 professional ) potete create questa store procedure e provare ad inviare una email attraverso SQL Server.

SQL Server usa tre potenti procedure di sistema (scritte in T-SQL) per gestire gli oggetti COM:

1 – sp_OACreate

2 – sp_OAMethod

3 – sp_OADestroy

che permettono di istanziare gli oggetti e richiamarne i metodi.

CREATE PROCEDURE sp_sendmail(

@indirizzo VARCHAR(255),

@messaggio VARCHAR(8000),

@soggetto VARCHAR(255),

@da VARCHAR(255) = ‘mio@indirizzo.it’) AS

DECLARE @CDO INT, @OLEResult INT, @Out INT

–Crea l’oggetto CDONTS.NewMail

EXECUTE @OLEResult = sp_OACreate ‘CDONTS.NewMail’, @CDO OUT

IF @OLEResult <> 0 PRINT ‘CDONTS.NewMail’

–Chiama il metodo send dell’oggetto

EXECUTE @OLEResult = sp_OAMethod @CDO, ‘Send’, NULL, @da, @indirizzo, @soggetto, @messaggio, 0

IF @OLEResult <> 0 PRINT ‘Send’

–Distruggi l’oggetto CDO

EXECUTE @OLEResult = sp_OADestroy @CDO

RETURN @OLEResult

GO

— Esempio per lanciare la procedura

–execute sp_sendmail ‘luca_milan@freeasp.it’,’Prova email per il corso TSQL’,’Email spedita con SQL Server’,’tuo@indirizzo.it’

L’esempio ci illustra un modo per sommare valori di tempo in formato hh:mm (ore:minuti) dalle colonne di una tabella.

–>creo tabella temporanea

create table #t ( hm varchar(5))

insert #t values(‘8:30’)

insert #t values(‘5:45’)

insert #t values(’12:12′)

insert #t values(‘7:20’)

select convert(varchar, sum(datediff(ss, ‘1900-01-01 00:00’, convert(datetime, hm)))/3600) +

‘:’ +

convert(varchar, (sum(datediff(ss, ‘1900-01-01 00:00’, convert(datetime, hm)))%3600)/60)

from #t

–>elimino tabella temporanea

drop table #t

Questa soluzione piuttosto elegante non usa cursori o tabelle temporanee, ma sfrutta la potenza delle tabelle derivate.

USE pubs

GO

–> Prendiamo il nome ed il cognome del 12-13-14 autore dalla tabella authors

select a1.au_fname + ‘ ‘ + a1.au_lname [Nome e Cognome],

(select count(*) from authors a2

where a2.au_lname <= a1.au_lname) [Lista degli autori]

from authors a1

where (select count(*) from authors a2

where a2.au_lname <= a1.au_lname) between 12 and 14

order by a1.au_lname

Assumiamo che SQL SERVER abbia impostato di default la domenica cioè 7 (è il caso del mio SQL Server) il parametro DATEFIRST che indica il primo giorno di della settimana.

–>Set datefirst 7

declare @d datetime, @l datetime

set @d = ’13/06/2001′ –> 13 Giugno 2001

–> Il sistema imposta l’inizio della settimana il:

select @@DATEFIRST as [Primo giorno della settimana] –> 1 lunedì, 2 martedì, … 7 domenica

–> Visualizziamo la data del lunedì della settimana

select dateadd(dd, 2-datepart(dw, @d), @d) as [Lunedì della settima]

Con le funzioni per le date con T-SQL è facile manipolare le date ed ottenere alcune interessanti informazioni, nel nostro esempio con datediff() e dateadd() possiamo ricavare il numero di giorni contenuti in uno specifico mese ed anche l’ultimo giorno del mese n questione:

declare @mese int, @anno int, @data datetime

select @mese = 6, @anno = 2001

select @data = convert(varchar, @anno) + right(‘0′ + convert(varchar, @mese), 2) + ’01’

–> Per calcolare il numero di giorni di un mese

select datediff(d, @data, dateadd(m, 1, @data)) as “Numero di giorni”

–> per determinare l’ultimo giorno del mese.

select dateadd( d, -1 , dateadd( m , 1 , @data ) ) AS “Ultimo giorno del mese”

I trigger sono strumenti molto potenti e pericolosi, in questa situazione possiamo capire quale azione viene compiuta su una tabella di nome ‘test’:

USE pubs –> Usa il database pubs

GO

/* se esiste all’interno del database l’oggetto ‘test’ ed è una TABELLA */

IF OBJECTPROPERTY ( object_id(‘test’),’ISTABLE’) = 1

DROP TABLE test /* allora lo elimino */

/* se esiste all’interno del database l’oggetto ‘tr_test’ ed è un TRIGGER */

IF OBJECTPROPERTY ( object_id(‘tr_test’),’ISTRIGGER’) = 1

DROP TRIGGER tr_test /* allora lo elimino */

GO

/* creo una TABELLA dal nome ‘test’ */

CREATE TABLE test(i int)

GO

/* creo un TRIGGER dal nome ‘tr_test’ */

CREATE TRIGGER tr_test ON test FOR INSERT, UPDATE, DELETE AS

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

PRINT ‘UPDATE… hai aggiornato uno o più record’

ELSE

IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)

PRINT ‘INSERT… hai inserito uno o più record’

ELSE

PRINT ‘DELETE… hai eliminato uno o più record’

GO

/* Istruzioni DML per INSERT , UPDATE e DELETE */

INSERT test values(1)

UPDATE test set i = i + 1

DELETE test

Dopo aver creato il RecordSet, è possibile eliminare record per mezzo dell’istruzione:

<% rs.Delete %>

Se utilizzare un database Access, attenzione alle relazioni impostate