Per creare una Stored Procedure dobbiamo avviare il Management Studio, aprire la cartella Stored Procedure del nostro database e scelgiere la solita opzione Crea stored. A questo punto ci viene messo a disposizione un editor con alcune istruzioni già pronte:
Listato 1. Testo per creare una stored procedure
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
RETURN <@Param1, sysname, @p1>
END
GO
Come si nota la Stored procedure comprende una prima sezione per la dichiarazione di parametri che possono essere di input oppure di output. Una seconda sezione che esegue una o più istruzione SQL ed infine la restituzione del parametro stesso.
Utilizzo dei parametri
La caratteristica più importante delle stored è che sono delle procedure in grado di ricevere e restituire parametri. In questo modo abbiamo la possibilità di riciclare il nostro codice e parametrizzarlo.
Per esempio, se volessimo creare una Stored che ci faccia visualizzare solamente gli orgini con una cifra superiore ad un parametro che vogliamo noi:
Listato 2. Stored Procedure ORDINI_PAR
USE MyDatabase
GO
CREATE PROCEDURE ORDINI_PAR
-- parametro prezzo di tipo money
<strong>@PREZZO money = 0 </strong>
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM ANA_ORDINI
WHERE ORD_PREZZO >= @PREZZO
END
GO
Se volessimo richiamare questa stored, potremmo farlo, ad esempio, per sapere quanti ordini hanno i prodotti che superano i 100 €. Aprendo un semplice editor di query bisogna dichiarare il parametro ed eseguire la stored.
Listato 3. Esecuzione Stored ORDINI_PAR
USE [MyDatabase]
GO
EXEC [dbo].[ORDINI_PAR]
@PREZZO = 0
GO
Stored Procedure per modificare i dati
Le Stored Procedure, oltre ad essere utilizzate per restituire delle informazioni dal database, possono essere utilizzate anche per modificare o aggiungere dati al database. Possiamo, per esempio, usare una Stored Procedure per inserire un nuovo record e di questo, prelevare l'ID per renderlo visibile all'applicazione.
Nell'esempio che segue dobbiamo creare una Stored in grado di inserire un nuovo ordine nel database e informarci sull'ID creato.
Listato 4. Stored INSERISCI_ORDINE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE INSERISCI_ORDINE
@ORDINE_ID INT OUTPUT,
@ID_UTENTE INT,
@CODICE VARCHAR(50),
@PRODOTTO VARCHAR(100),
@QUANTITA INT,
@PREZZO MONEY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ORDINI
(ORDINI.ORD_ID_ANA,
ORDINI.ORD_CODICE,
ORDINI.ORD_PRODOTTO,
ORDINI.ORD_QUANTITA,
ORDINI.ORD_PREZZO)
VALUES (@ID_UTENTE,
@CODICE, @PRODOTTO, @QUANTITA, @PREZZO);
SET @ORDINE_ID = (SELECT @@IDENTITY);
END
GO