MSDE Altre istruzioni per l'uso (parte 7) - Creare/Eliminare Una Stored Procedure
a cura di Sabrina Cosolo (requisiti: Livello intemedio di conoscenza generale)

Creare una Stored Procedure Via Codice SQL
Nel comune uso dei database una delle necessità principali dell'amministratore del database è quella di mantenere la consistenza dei dati inseriti, per questo solitamente si utilizzano le stored procedure per la modifica, l'inserimento, la manipolazione dei dati in quanto è possibile scrivervi il codice necessario a validare l'input prima che il database venga aggiornato. E' possibile inoltre utilizzare le transazioni esplicite per controllare automaticamente il mantenimento della consistenza. Il numero dei possibili usi delle stored procedure è infinito e l'esempio da noi scritto è veramente minimo, vi invitiamo a consultare i Books Online di SQL Server per trovare esempi e spiegazioni più dettagliate.

Oltre che per il controllo e la validazione dell'input una stored procedure può essere utilizzata per elaborare dati di sintesi, per inserimento di dati su tabelle multiple, per generare nuove tabelle, per aggiornare e valorizzare dati e chi più ne ha più ne metta. Lo scopo di questo articolo è spiegare la sintassi per la creazione delle procedure, non il loro uso perciò ecco l'esempio:

  CREATE PROCEDURE
    SPRiportaInterprete  
    ( @IDInterprete int = null  
    , @IDCd int = null )  
  AS  
    begin tran  
      Update tbBrani  
        SET [idInterprete]=@idInterprete  
        WHERE [idCd]=@idCd and ([idInterprete] is null )  
    commit tran  
  RETURN

La sintassi è la seguente:

  CREATE Procedure [owner].NomeProcedura 
    ( InputParameter1, ..., InputParameterN
    , OutputParameter OUTPUT)
  AS
    Codice SQL da eseguire
  RETURN

dove

• NomeProcedura è un nome che deve essere univoco all'interno del database e deve rispondere alle regole di nomenclatura già citate in precedenza.
• InputParameter1, ... InputParameterN: sono i parametri passati in ingresso alla procedura, nel nostro caso i valori da assegnare ai campi della tabella modificata, ogni parametro deve essere definito con la forma:
  Nome Tipo= valore di default
• OutputParameter: Il parametro di ritorno è naturalmente opzionale, se la procedura è costruita per generare un valore, questo valore deve essere inserito nel parametro di output affinché il programma chiamante possa leggerlo.
  Anche il parametro di output ha la sintassi di quelli di input a cui si aggiunge il termine OUTPUT per stabilire che deve essere restituito al chiamante.

Una volta creata, la procedura può essere utilizzata, ma il suo uso deve essere stabilito assegnando i diritti agli utenti del database, ovviamente SA e i Builtin/Administrator possono usare tutte le procedure.
Ricordo anche che è opportuno che, come tutti gli oggetti database, sia creata utilizzando un utente con diritti di amministrazione sul database, come SA o gli appartenenti al gruppo Builtin/Administrators.

Eliminare una Stored Procedure via codice SQL
Come sempre eliminare è semplice; ecco il codice per eliminare la vista che abbiamo creato:

  DROP Procedure SPRiportaInterprete

La sintassi di base del comando è:

  DROP PROCEDURE [owner].[NomeProcedura]

Dove owner e NomeProcedura corrispondono alle definizioni date in precedenza. Al contrario dei vincoli non c'è alcun controllo da parte dell'RDBMS sull'eliminazione della procedura, perciò, se quest'ultima fosse usata all'interno di altre procedure (normale quando si lavora su Database complessi), questo non verrebbe controllato e quindi potrebbe dar luogo a risultati indesiderati se non si presta attenzione.

Anche per questi comandi SQL si può utilizzare OSQL.EXE (vedi, anche per i parametri).

La sintassi per chiamare un comando SQL usando OSQL è la seguente:

  osql -S Localhost -U SA -P as -i vistesql.sql

un possibile file di input da utilizzare per realizzare uno degli esempi vistesql.sql è un file di testo contenente il comando da eseguire che è leggermente diverso da quello sopra descritto, riporto lo script per la creazione della nostra vista:

  USE MusicBox
  GO
 
  CREATE PROCEDURE
    SPRiportaInterprete  
    ( @IDInterprete int = null  
    , @IDCd int = null )  
  AS  
    begin tran  
      Update tbBrani  
        SET [idInterprete]=@idInterprete  
        WHERE [idCd]=@idCd and ([idInterprete] is null )  
    commit tran  
  RETURN
 
  GO
 
  QUIT

Il comando USE MUSICBOX serve a dire al server che dobbiamo lavorare sul Database Musicbox (database di esempio creato nel precedente articolo), Il comando GO esegue i comandi richiesti, il comando QUIT esce da OSQL.EXE che altrimenti si comporta come un interprete comandi e rimane attivo all'interno della finestra console.

La sintassi da usare con OSQL per trasportarvi tutti gli script da noi generati in questa pagina è la seguente:

  USE Musicbox
  GO 
 
  script da eseguire
 
  GO
 
  QUIT

<<< Creare/Eliminare Viste

Salvare/Trasportare Database >>>