Store procedure (Parte II)

II Parte dell'articolo. Leggi la I Parte

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

CREATE PROCEDURE usp_displayallusers
AS

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

SELECT * FROM USERLIST

Il che darà a tutto il codice questo aspetto:

 

/*
Name:  usp_displayallusers
Description:  displays all records and columns in USERLIST table
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_displayallusers

AS

SELECT * FROM USERLIST

 

Ecco fatto, la nostra stored procedure è pronta! Se l' abbiamo scritta, per comodità, in un editor testuale, è tempo di copiare ed incollare il tutto nella finestra New Stored Procedure dell' SQL Server, per poi premere il bottone "Check Syntax" , il che farà eseguire al server un controllo sulla sintassi, in modo da accertarci di non avere commesso errori di distrazione. Se avremo scritto tutto giusto riceveremo il messaggio "Syntax check successful!" e non ci resterà altro che dare l' OK e salvare quanto creato. Per avviarla, dovremo aprire il Query Analyzer e scrivere:

 

exec usp_displayallusers

clickando poi il bottone verde "Play".

Stored Procedures più complesse

Vedremo ora come creare stored procedures più complesse, integrandovi pure i comandi INSERT, UPDATE, per farle inserire dati nel database dei records. Per questo faremo uso di variabili.

Variabili Input

Sono svariati i motivi per i quali si può voler passare qualche parametro alla stored procedure, specialmente nel caso questa venga richiamata da una pagina dinamica. Le variabili di input servono appunto ad immagazzinare questi parametri, e sfruttarli nella query da eseguire. Per iniziare, basterà saper distinguere tra variabili di tipo Global e quelle di tipo Local. Ora come ora utilizzeremo solamente variabili Local. Da ricordare, che il nome della variabile va sempre preceduto dal carattere "@". Ecco alcuni esempio:

  • @f_name
  • @cognome
  • @indirizzo

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

Nel nostro esempio vogliamo aggiungere un record alla tabella USERLIST. 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

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

/*
Name: usp_adduser
Description: Adds a user
Author: Tom O’Neill
Modification Log: Change

Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
*/

CREATE PROCEDURE usp_adduser

A questo punto dobbiamo occuparci delle variabili, e crearne una per ciascun parametro della query che vogliamo venga eseguita; non sarà necessario, ogni volta, assegnare un valore a tutte le variabili dichiarate, ma dobbiamo comunque dare la possibilità alla stored procedure di venir editata in ogni suo campo. Creeremo una variabile per ciascuna colonna della tabella USERLIST, partendo dal presupposto che ciascuna colonna possa contenere il carattere NULL; nel caso qualcuna non lo supporti, e alla relativa variabile non viene associato alcun valore, la stored procedure provvederà ad inserirvi un carattere di default.Ecco la lista delle variabili e dei campi ai quali sono 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

Il codice da inserire nella stored procedure, sotto "CREATE PROCEDURE usp_adduser" sarà quindi:

CREATE PROCEDURE usp_adduser

@login
@pswd
@f_name
@l_name
@address_1
@address_2
@city
@state
@zipcode
@email

Ma così non basta; dobbiamo specificare quale tipologie di dati ciascuna di esse potrà contenere ("varchar" , "char" ,"numeric" , ecc). Il codice aggiornato risulterà quindi il seguente:

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)

A questo punto bisogna spiegare alla stored procedure il suo compito; inseriamo quindi il comando "AS" dopo l' elenco delle variabili, ed iniziamo con il codice della query che, dovendo inserire un nuovo record nella tabella 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)

La nostra nuova stored procedure sarà quindi la seguente:

/*
Name: usp_adduser
Description: Add new logins.
Author: Tom O’Neill
Modification Log: Change

Description Date Changed By
Created 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)

AS

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)

A questo punto non ci resta che controllarne la sintassi, per evitare brutte sorprese dettate dalla distrazione, e salvare il tutto, come già fatto in precedenza.

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

Il codice di chiamata standard per una stored procedure, l' abbiamo già visto è "exec usp_adduser"; nel caso la stored accetti parametri dall' esterno, non bisognerà far altro che scriverli tra due caratteri ', in questo modo:

exec usp_adduser 'guido'

Il comando con questa sintassi chiamerà la stored procedure usp_adduser passandole come primo parametro (@login) la stringa "guido". Ecco come richiamarla passandole tutti i parametri:

exec usp_adduser 'guido', 'miapassword', 'Delfins', 'Blog', 'Via XX Settembre', ' ', 'Genova', 'GE', '16040', 'email@email.it'

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

Vediamo ora qualche altra stored procedure abbastanza complessa ed accattivante.

Variabili input con i comandi SELECT ed UPDATE

Diamo un' occhiata a questa stored procedure:

/*
Name: usp_updateuser
Description: Updates user information
Author: Tom O’Neill
Modification Log: Change

Description Date Changed By
Created 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)

AS

UPDATE USERLIST

SET

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

WHERE usr_id=@usr_id

Come possiamo notare, è stata dichiarata una variabile in più, @usr_id avente datatype "int" in quanto, intuiamo, dovrà contenere solamente valori numerici. Come mai non l' abbiamo usata nella precedente stored procedure? Perchè il comando INSERT attribuisce di default un id a ciascun record che viene inserito.In questo caso, però, vogliamo modificare un record esistente, e sarà quindi l' utente a dover specificare quale record modificare, il che lo specificherà appunto per mezzo di tal variabile.

Non ci rimane che parlare del comando SELECT, che anch' esso sarà legato ad una o più variabili. Ecco il codice di una semplice stored procedure che ne fa utilizzo:

/*
Name: usp_finduser
Description: find a user
Author: Tom O’Neill
Modification Log: Change

Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
*/



CREATE PROCEDURE usp_finduser

@usr_id int

AS

SELECT * FROM USERLIST
WHERE usr_id=@usr_id

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

exec usp_finduser '1'

A questo punto abbiamo tutte le conoscenze-base per sbizzarrirci e creare le nostre, personali, stored procedures.



Ti potrebbe interessare anche

commenta la notizia

Ci sono 1 commenti
Anonimo
Complimenti davvero. Finalmente una guida semplice ed essenziale per avere un'infarinatura basilare del mondo delle store procedure senza googlare a destra e sinistra con pezzi di codice copiaincollati di qua e di là. Grazie.