Procedura: creare stored procedure e funzioni definite dall'utente

Aggiornamento: novembre 2007

Per creare nuove stored procedure è possibile utilizzare Esplora server. Le stored procedure consentono di definire regole business, di controllare la modifica dei dati, limitare l'accesso tramite autorizzazioni di protezione, garantire l'integrità delle transazioni e svolgere in genere le funzioni di database richieste dall'applicazione.

Con Microsoft SQL Server 2000 e versioni successive è possibile utilizzare Esplora server anche per creare funzioni definite dall'utente. Una funzione definita dall'utente è una routine che comprende una logica da utilizzare per altre query. Mentre le viste sono limitate a una singola istruzione SELECT, le funzioni definite dall'utente possono prevedere più istruzioni SELECT e garantiscono una logica più efficace rispetto alle viste.

Le funzioni definite dall'utente restituiscono sempre un valore. In base al tipo di valore restituito, ogni funzione definita dall'utente rientra in una delle tre seguenti categorie:

• Funzione con valori scalari   Una funzione definita dall'utente può restituire un valore scalare quale un valore integer o un valore timestamp. Se una funzione restituisce un valore scalare è possibile utilizzarla in una query in qualsiasi punto si desideri utilizzare un nome di colonna.

• Funzione inline   Se una funzione definita dall'utente contiene una sola istruzione SELECT e questa istruzione è aggiornabile, potrà essere aggiornato anche il risultato in formato tabella restituito dalla funzione. Tali funzioni vengono chiamate funzioni inline. Quando una funzione inline restituisce una tabella, è possibile utilizzare tale funzione nella clausola FROM di un'altra query. Per ulteriori informazioni, vedere Procedura: creare query mediante l'utilizzo di altre origini oltre a una tabella.

• Funzione con valori di tabella   Se una funzione definita dall'utente contiene più di un'istruzione SELECT o contiene un'istruzione SELECT non aggiornabile, non sarà possibile aggiornare il risultato in formato tabella restituito dalla funzione. Quando una funzione con valori di tabella restituisce una tabella, è possibile utilizzare tale funzione nella clausola FROM di un'altra query.

Nota:

Negli esempi di seguito riportati dbo è l'acronimo di database owner, proprietario del database, e viene utilizzato per qualificare il nome della stored procedure e della funzione definita dall'utente. Il dbo è un utente con permessi impliciti per eseguire tutte le attività del database. Ogni oggetto creato da qualsiasi membro del ruolo sysadmin appartiene automaticamente al dbo. Negli esempi che seguono è incluso il qualificatore del nome dbo.

Nota:
Le finestre di dialogo e i comandi di menu visualizzati possono differire da quelli descritti nella Guida a seconda delle impostazioni attive o dell'edizione in uso. Per modificare le impostazioni, scegliere Importa/Esporta impostazioni dal menu Strumenti. Per ulteriori informazioni, vedere Impostazioni di Visual Studio.

Per creare una nuova stored procedure

1. In Esplora server fare clic con il pulsante destro del mouse sulla cartella Stored procedure o su una delle stored procedure nella cartella.

2. Scegliere Aggiungi nuova stored procedure dal menu di scelta rapida.

   Verrà creata una nuova stored procedure con le seguenti istruzioni SQL di base:

   CREATE PROCEDURE dbo.StoredProcedure1
   /*
      (
         @parameter1 datatype = default value,
         @parameter2 datatype OUTPUT
      )
   */
   AS
      /* SET NOCOUNT ON */
      RETURN
   

3. È possibile sostituire StoredProcedure1 nella prima riga con il nome della nuova routine. È ad esempio possibile utilizzare il nome MyProcedure:

   CREATE PROCEDURE dbo.MyProcedure
   

   Nota:
   Le stored procedure devono avere un nome univoco. Se si sceglie un nome che è già stato assegnato a un'altra stored procedure, un messaggio di errore avvertirà che esiste già una stored procedure con quel nome.

4. Scrivere il testo rimanente della procedura in SQL.

   Per ulteriori informazioni ed esempi di stored procedure, vedere la documentazione relativa al server di database in uso. Se si utilizza Microsoft® SQL Server™, vedere l'argomento relativo alla creazione di procedure nella documentazione in linea di SQL Server.

Per creare una nuova funzione definita dall'utente

1. In Esplora server fare clic con il pulsante destro del mouse sulla cartella Funzioni o su una delle funzioni nella cartella.

2. Scegliere Aggiungi nuovo, quindi Funzione inline, Funzione con valori di tabella o Funzione con valori scalari dal menu di scelta rapida.

   Nota:
   Non è possibile modificare le istruzioni SQL di base per una nuova funzione nelle istruzioni SQL di un tipo di funzione differente e salvare i risultati. Se, ad esempio, si comincia con una funzione inline non sarà possibile modificare le istruzioni SQL in una funzione con valori scalari. Il processo di salvataggio non verrà completato correttamente.

   Verrà creata una funzione definita dall'utente utilizzando le istruzioni SQL di base. Scegliendo ad esempio Funzione con valori scalari, verranno visualizzate le seguenti istruzioni SQL di base:

   CREATE FUNCTION dbo.Function1
      (
      /*
      @parameter1 datatype = default value,
      @parameter2 datatype
      */
      )
   RETURNS /* datatype */
   AS
      BEGIN
         /* sql statement ... */
      RETURN /* value */
      END
   

3. È possibile sostituire Function1 nella prima riga con il nome della nuova funzione. È ad esempio possibile utilizzare il nome MyFunction:

   CREATE FUNCTION dbo.MyFunction
   

   Nota:
   Le funzioni definite dall'utente devono avere un nome univoco. Se si sceglie un nome che è già stato assegnato a un'altra funzione, un messaggio di errore avvertirà che esiste già una funzione con quel nome.

4. Scrivere il testo rimanente della funzione in SQL.

Nota:
Se nel menu di scelta rapida non sono disponibili le opzioni desiderate, è possibile che la versione di Visual Studio in uso non supporti questa funzionalità. Per ulteriori informazioni, vedere Edizioni di Visual Database Tools.

Per ulteriori informazioni ed esempi di funzioni definite dall'utente, vedere la documentazione relativa al server di database in uso. Se si utilizza Microsoft® SQL Server™, vedere l'argomento relativo alla creazione di funzioni nella documentazione in linea di SQL Server.

Vedere anche

Concetti

Espressioni nelle query

Altre risorse

Utilizzo di stored procedure e di funzioni definite dall'utente