• Programmazione » Database » SQL Server - Guida, manuale pratico e tutorial per database Microsoft

IIParte dell'articolo. Leggi la IParte

Comunemente lavariabili vengono dichiarate dopo la riga "CREATEPROCEDURE" e, se la nostra stored procedure non richiedela chiamata in causa di variabili, basterà scrivere"AS":

CREATE PROCEDURE usp_displayallusersAS

Aquesto punto, sotto "AS" dobbiamo scrivere ilcodice SQL da far eseguire alla stored procedure.Innanzitutto, usiamo il comando "SELECT"

SELECT * FROM USERLIST

Il chedarà a tutto il codice questo aspetto:

/*Name:  usp_displayallusersDescription:  displays all records and columns in USERLIST tableAuthor:  Tom O’NeillModification Log: ChangeDescriptionDate   Changed ByCreated procedure7/15/2003    Tom O’Neill*/CREATE PROCEDURE usp_displayallusersASSELECT * FROM USERLIST

Eccofatto, la nostra stored procedure è pronta! Se l'abbiamo scritta, per comodità, in un editor testuale,è tempo di copiare ed incollare il tutto nellafinestra New Stored Procedure dell' SQL Server, per poipremere il bottone "Check Syntax" , il chefarà eseguire al server un controllo sulla sintassi,in modo da accertarci di non avere commesso errori didistrazione. Se avremo scritto tutto giusto riceveremo ilmessaggio "Syntax check successful!" e non ciresterà altro che dare l' OK e salvare quantocreato. Per avviarla, dovremo aprire il Query Analyzer escrivere:

execusp_displayallusers

clickando poi ilbottone verde "Play".

StoredProcedures più complesse

Vedremo ora comecreare stored procedures più complesse, integrandovipure i comandi INSERT, UPDATE, per farle inserire dati neldatabase dei records. Per questo faremo uso divariabili.

VariabiliInput

Sonosvariati i motivi per i quali si può voler passarequalche parametro alla stored procedure, specialmente nelcaso questa venga richiamata da una pagina dinamica. Levariabili di input servono appunto ad immagazzinare questiparametri, e sfruttarli nella query da eseguire. Periniziare, basterà saper distinguere tra variabili ditipo Global e quelle di tipo Local. Ora come ora utilizzeremosolamente variabili Local. Da ricordare, che il nome dellavariabile va sempre preceduto dal carattere "@".Ecco alcuni esempio:

• @f_name
• @cognome
• @indirizzo

Dichiararle,è semplice: come abbiamo già detto non bisognafar altro che aggiungere il nome ed il tipo di variabile(text, integer, ecc) subito sotto il testo "CREATEPROCEDURE").

Nelnostro esempio vogliamo aggiungere un record alla tabellaUSERLIST. Dobbiamo tener presente che:

• "usr_id" è la chiave primaria ed è auto-generata dal sistema; non richiede pertanto alcuna variabile;
• "login" , "pswd" , "l_name" , and "email" sono invece richiesti

Lanuova stored procedure la chiameremo "usp_adduser"e quindi il codice base sarà:

/*Name: usp_adduserDescription: Adds a userAuthor: Tom O’NeillModification Log: ChangeDescription Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/CREATE PROCEDURE usp_adduser

Aquesto punto dobbiamo occuparci delle variabili, e crearneuna per ciascun parametro della query che vogliamo vengaeseguita; non sarà necessario, ogni volta, assegnareun valore a tutte le variabili dichiarate, ma dobbiamocomunque dare la possibilità alla stored procedure divenir editata in ogni suo campo. Creeremo una variabile perciascuna colonna della tabella USERLIST, partendo dalpresupposto che ciascuna colonna possa contenere il carattereNULL; nel caso qualcuna non lo supporti, e alla relativavariabile non viene associato alcun valore, la storedprocedure provvederà ad inserirvi un carattere didefault.Ecco la lista delle variabili e dei campi ai qualisono associate:

• @login—login
  
   

• @pswd—pswd
  
   

• @f_name—f_name
  
   

• @l_name—l_name
  
   

• @address_1—address_1
  
   

• @address_2—address_2
  
   

• @city—city
  
   

• @state—state
  
   

• @zipcode—zipcode
  
   

• @email—email

Ilcodice da inserire nella stored procedure, sotto "CREATEPROCEDURE usp_adduser" sarà quindi:

CREATE PROCEDURE usp_adduser@login@pswd@f_name@l_name@address_1@address_2@city@state@zipcode@email

Macosì non basta; dobbiamo specificare quale tipologiedi dati ciascuna di esse potrà contenere("varchar" , "char" ,"numeric", ecc). Il codice aggiornato risulterà quindi ilseguente:

CREATE PROCEDURE usp_adduser@login varchar(20),@pswd varchar(20),@f_name varchar(25),@l_name varchar(35),@address_1 varchar(30),@address_2 varchar(30),@city varchar(30),@state char(2),@zipcode char(10),@email varchar(50)

Aquesto punto bisogna spiegare alla stored procedure il suocompito; inseriamo quindi il comando "AS" dopol' elenco delle variabili, ed iniziamo con il codicedella query che, dovendo inserire un nuovo record nellatabella sarà questo:

INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)

Lanostra nuova stored procedure sarà quindi laseguente:

/*Name: usp_adduserDescription: Add new logins.Author: Tom O’NeillModification Log: ChangeDescription Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/CREATE PROCEDURE usp_adduser@login varchar(20),@pswd varchar(20),@f_name varchar(25),@l_name varchar(35),@address_1 varchar(30),@address_2 varchar(30),@city varchar(30),@state char(2),@zipcode char(10),@email varchar(50)ASINSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)

Aquesto punto non ci resta che controllarne la sintassi, perevitare brutte sorprese dettate dalla distrazione, e salvareil tutto, come già fatto in precedenza.

Abbiamo creatocosì una stored procedure che accetta dati dall'esterno. Ma come passarle questi parametri?

Ilcodice di chiamata standard per una stored procedure, l'abbiamo già visto è "execusp_adduser"; nel caso la stored accetti parametridall' esterno, non bisognerà far altro chescriverli tra due caratteri ', in questo modo:

execusp_adduser 'guido'

Ilcomando con questa sintassi chiamerà la storedprocedure usp_adduser passandole come primo parametro(@login) la stringa "guido". Ecco come richiamarlapassandole tutti i parametri:

execusp_adduser 'guido', 'miapassword','Delfins', 'Blog', 'Via XXSettembre', ' ', 'Genova', 'GE','16040', 'email@email.it'

Eseguendo questocomando, il server SQL ci risponderà che èstato aggiunto un nuovo record alla tabella.

Vediamo oraqualche altra stored procedure abbastanza complessa edaccattivante.

Variabiliinput con i comandi SELECT ed UPDATE

Diamoun' occhiata a questa stored procedure:

/*Name: usp_updateuserDescription: Updates user informationAuthor: Tom O’NeillModification Log: ChangeDescription Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/CREATE PROCEDURE usp_updateuser@usr_id int,@login varchar(20),@pswd varchar(20),@f_name varchar(25),@l_name varchar(35),@address_1 varchar(30),@address_2 varchar(30),@city varchar(30),@state char(2),@zipcode char(10),@email varchar(50)ASUPDATE USERLISTSETlogin=@login,pswd=@pswd,f_name=@f_name,l_name=@l_name,address_1=@address_1,address_2=@address_2,city=@city,state=@state,zipcode=@zipcode,email=@emailWHERE usr_id=@usr_id

Comepossiamo notare, è stata dichiarata una variabile inpiù, @usr_id avente datatype "int" inquanto, intuiamo, dovrà contenere solamente valorinumerici. Come mai non l' abbiamo usata nella precedentestored procedure? Perchè il comando INSERT attribuiscedi default un id a ciascun record che viene inserito.Inquesto caso, però, vogliamo modificare un recordesistente, e sarà quindi l' utente a doverspecificare quale record modificare, il che lospecificherà appunto per mezzo di talvariabile.

Non cirimane che parlare del comando SELECT, che anch' essosarà legato ad una o più variabili. Ecco ilcodice di una semplice stored procedure che ne fautilizzo:

/*Name: usp_finduserDescription: find a userAuthor: Tom O’NeillModification Log: ChangeDescription Date Changed ByCreated procedure 7/15/2003 Tom O’Neill*/CREATE PROCEDURE usp_finduser@usr_id intASSELECT * FROM USERLISTWHERE usr_id=@usr_id

Unavolta controllata la sintassi e salvata la stored procedure,la potremo eseguire mediante il comando

execusp_finduser '1'

Aquesto punto abbiamo tutte le conoscenze-base persbizzarrirci e creare le nostre, personali, storedprocedures.