Attraverso l’istruzione ALTER TABLE è possibile aggiungere una chiave primaria ad una tabella che abbiamo già creato in precedenza. Ogni volta che viene creata una PK in questo modo SQL server aggiunge in modo automatico un indice associato alla PK (attenzione di default l’indice è CLUSTERED):

BEGIN TRAN

USE pubs

ALTER TABLE MioTest

ADD CONSTRAINT PK_MioTest

PRIMARY KEY (ID)

COMMIT TRAN

Attraverso una stored procedure di sistema dal nome sp_password, possiamo facilmente cambiare le password alle nostre login:

/*

EXEC = esegue la procedura

*/

EXEC sp_password @old = “vecchiapassword”, @new = “nuovapassword”, @loginame = “miologin”

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