• Articolo

Progettazione di stored procedure (Motore di database)

Per creare una stored procedure è possibile utilizzare quasi tutte le istruzioni Transact-SQL che è possibile scrivere come batch.

Regole per la progettazione di stored procedure

Di seguito sono riportate le regole per la progettazione di stored procedure:

  • La definizione di CREATE PROCEDURE può includere qualsiasi numero e tipo di istruzioni SQL, tranne le istruzioni seguenti, che non possono essere utilizzate in qualsiasi punto di una stored procedure.

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE o ALTER FUNCTION

    CREATE o ALTER TRIGGER

    CREATE o ALTER PROCEDURE

    CREATE o ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

     

  • In una stored procedure è possibile creare altri oggetti di database. È possibile fare riferimento a un oggetto creato nella stessa stored procedure purché sia già stato creato prima dell'impostazione del riferimento.

  • In una stored procedure è possibile fare riferimento a tabelle temporanee.

  • Se si crea una tabella temporanea locale in una stored procedure, la tabella temporanea esiste soltanto per i fini della stored procedure e viene eliminata quando si esce dalla stored procedure.

  • Se si esegue una stored procedure che chiama un'altra stored procedure, la stored procedure chiamata può accedere a tutti gli oggetti creati dalla prima stored procedure, incluse le tabelle temporanee.

  • Se si esegue una stored procedure remota che apporta modifiche in un'istanza remota di Microsoft SQL Server, non è possibile eseguire il rollback delle modifiche. Le stored procedure remote non partecipano alle transazioni.

  • Il numero massimo di parametri consentiti in una stored procedure è 2100.

  • Il numero massimo di variabili locali consentite in una stored procedure è limitato soltanto dalla memoria disponibile.

  • Compatibilmente con la memoria disponibile, le dimensioni massime di una stored procedure sono di 128 MB.

Qualifica dei nomi nelle stored procedure

In una stored procedure, i nomi di oggetto utilizzati con le istruzioni (ad esempio, SELECT o INSERT) che non vengono qualificati con uno schema, per impostazione predefinita vengono qualificati in base allo schema della stored procedure. Se l'utente che crea una stored procedure non qualifica i nomi delle tabelle o delle viste a cui si fa riferimento nelle istruzioni SELECT, INSERT, UPDATE o DELETE della stored procedure, per impostazione predefinita l'accesso a tali tabelle tramite la stored procedure viene limitato all'autore della procedura.

I nomi di oggetto utilizzati con tutte le istruzioni DDL (Data Definition Language), ad esempio le istruzioni CREATE, ALTER o DROP, con le istruzioni DBCC e con le istruzioni EXECUTE e le istruzioni SQL dinamiche devono essere qualificate con il nome dello schema degli oggetti se la stored procedure deve essere utilizzata da altri utenti. Specificando il nome dello schema per tali oggetti si ha la sicurezza che il nome venga risolto nello stesso oggetto indipendentemente da chi chiama la stored procedure. Se non si specifica il nome di uno schema, SQL Server cercherà di risolvere il nome dell'oggetto utilizzando prima lo schema predefinito del chiamante o l'utente specificato nella clausola EXECUTE AS, quindi lo schema dbo.

Offuscamento delle definizioni delle procedure

Per convertire il testo originale dell'istruzione CREATE PROCEDURE in un formato offuscato, utilizzare l'opzione WITH ENCRYPTION. L'output dell'offuscamento non è direttamente visibile in nessuna delle tabelle o viste di sistema in SQL Server 2008: gli utenti che non dispongono dell'accesso alle tabelle di sistema, alle viste di sistema o ai file di database non possono recuperare il testo offuscato. Tuttavia, il testo è disponibile agli utenti con privilegi di accesso diretto ai file di database. Questi utenti potrebbero essere in grado di decodificare l'offuscamento per recuperare il testo originale della definizione della stored procedure.

Opzioni dell'istruzione SET

Motore di database salva le impostazioni delle opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS quando viene creata o modificata una stored procedure Transact-SQL. Queste impostazioni originali vengono utilizzate quando viene eseguita la stored procedure. Le impostazioni delle sessioni client per le opzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS pertanto vengono ignorate durante l'esecuzione della stored procedure. Le istruzioni SET QUOTED_IDENTIFIER e SET ANSI_NULLS all'interno della stored procedure non ne influenzano la funzionalità.

Altre opzioni SET, ad esempio SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS, non vengono salvate quando viene creata o modificata una stored procedure. Se la logica della stored procedure dipende da una particolare impostazione, includere un'istruzione SET all'inizio della procedura per assicurare che venga utilizzata l'impostazione adeguata. Quando un'istruzione SET viene eseguita da una stored procedure, l'impostazione rimane attiva fino al completamento della stored procedure. Viene quindi ripristinato il valore che era assegnato all'impostazione quando è stata chiamata la stored procedure. In tal modo i singoli client possono impostare le opzioni desiderate senza che questo abbia ripercussioni sulla logica della stored procedure.

Nota

ANSI_WARNINGS non viene applicata quando vengono trasmessi parametri in una stored procedure, una funzione definita dall'utente oppure in caso di dichiarazione e impostazione delle variabili in un'istruzione batch. Se, ad esempio, la variabile viene definita come char(3) e quindi impostata su un valore maggiore di tre caratteri, i dati verranno troncati alla dimensione definita e l'istruzione INSERT o UPDATE avrà esito positivo.