Stored procedure (Motore di database)

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

In SQL Server una stored procedure è un gruppo di una o più istruzioni di Transact-SQL oppure un riferimento a un metodo CLR (Common Runtime Language) di Microsoft .NET Framework. Le stored procedure assomigliano ai costrutti di altri linguaggi di programmazione perché possono essere utilizzate per:

• Accettare parametri di input e restituire più valori sotto forma di parametri di output al programma che esegue la chiamata.

• Includere istruzioni di programmazione che eseguono operazioni nel database, tra cui la chiamata di altre stored procedure.

• Restituire un valore di stato a un programma che esegue la chiamata per indicare l'esito positivo o negativo (e il motivo dell'esito negativo).

Vantaggi dell'utilizzo delle stored procedure

Nell'elenco seguente vengono descritti alcuni vantaggi dell'utilizzo di stored procedure.

Riduzione del traffico di rete server/client

I comandi in una stored procedure vengono eseguiti come un solo batch di codice. In questo modo è possibile ridurre significativamente il traffico di rete tra il server e il client perché solo la chiamata per eseguire la stored procedure viene inviata attraverso la rete. Senza l'incapsulamento del codice consentito dalla stored procedure, la rete viene attraversata da ogni singola riga di codice.

Maggiore sicurezza

Tramite una stored procedure, più utenti e programmi client sono in grado di eseguire operazioni su oggetti di database sottostanti, anche se gli utenti e i programmi non dispongono di autorizzazioni dirette su tali oggetti sottostanti. La stored procedure consente di controllare quali processi e attività vengono eseguiti e di proteggere gli oggetti di database sottostanti. In questo modo si elimina la necessità di concedere autorizzazioni a livello di singolo oggetto, semplificando i livelli di sicurezza.

È possibile specificare la clausola EXECUTE AS nell'istruzione CREATE PROCEDURE per consentire la rappresentazione di un altro utente o consentire a utenti o applicazioni di eseguire alcune attività nel database senza il bisogno di autorizzazioni dirette per gli oggetti e i comandi sottostanti. Per alcune azioni ad esempio, come TRUNCATE TABLE, non è possibile concedere le autorizzazioni. Per eseguire TRUNCATE TABLE è necessario che l'utente disponga delle autorizzazioni ALTER per la tabella specificata. Non è consigliabile concedere a un utente le autorizzazioni ALTER su una tabella, perché l'utente disporrebbe di autorizzazioni ben superiori alla semplice possibilità di troncare la tabella. Incorporando l'istruzione TRUNCATE TABLE in un modulo e specificando che tale modulo venga eseguito come un utente che dispone di autorizzazioni per la modifica della tabella, è possibile estendere le autorizzazioni per il troncamento della tabella all'utente al quale si concedono le autorizzazioni EXECUTE sul modulo.

Quando un'applicazione chiama una stored procedure attraverso la rete, solo la chiamata per eseguire la stored procedure è visibile. Pertanto, gli utenti malintenzionati non possono visualizzare i nomi di oggetti database e tabelle, né incorporare le istruzioni di Transact-SQL personalizzate o cercare dati critici.

L'utilizzo dei parametri della stored procedure aiuta a proteggersi da attacchi SQL injection. Dal momento che l'input del parametro viene trattato come un valore letterale e non come codice eseguibile, è più difficile che un utente malintenzionato riesca a inserire un comando nelle istruzioni di Transact-SQL all'interno della stored procedure compromettendo la sicurezza.

È possibile crittografare le stored procedure, nascondendo il codice sorgente. Per altre informazioni, vedere Crittografia di SQL Server.

Riutilizzo del codice

Il codice di operazioni ripetitive sul database risulta assolutamente appropriato ai fini dell'incapsulamento nelle stored procedure. In questo modo si elimina la necessità di riscrivere più volte lo stesso codice, si riducono le incoerenze al suo interno e se ne consentono l'accesso e l'esecuzione da parte di qualsiasi utente o applicazione che disponga delle autorizzazioni necessarie.

Semplificazione della manutenzione

Quando si effettua la chiamata delle stored procedure tramite le applicazioni client e si mantengono le operazioni nel database solo nel livello dati, in caso di modifiche nel database sottostante è necessario aggiornare unicamente le stored procedure. Si mantiene separato il livello applicazione senza che venga interessato dalle modifiche a layout del database, relazioni o processi.

Prestazioni migliorate

Per impostazione predefinita, una stored procedure viene compilata solo alla prima esecuzione e si crea un piano di esecuzione riutilizzato nelle esecuzioni successive. Dal momento che non è necessaria la creazione di un nuovo piano da parte del sistema di elaborazione delle query, l'elaborazione della stored procedure richiede generalmente un tempo minore.

Se sono presenti modifiche significative alle tabelle o ai dati a cui fa riferimento la stored procedure, il piano precompilato potrebbe determinare in realtà un'esecuzione più lenta della stored procedure. In questo caso, la ricompilazione della stored procedure e la forzatura di un nuovo piano di esecuzione possono migliorare le prestazioni.

Tipi di stored procedure

personalizzato

È possibile creare una stored procedure definita dall'utente in un database definito dall'utente o in tutti i database di sistema a eccezione del database Resource. La stored procedure può essere sviluppata in Transact-SQL o come un riferimento a un metodo CLR (Common Runtime Language) di Microsoft .NET Framework.

Temporanea

Le stored procedure temporanee sono un tipo di stored procedure definite dall'utente. Le stored procedure temporanee sono come una stored procedure permanente, tranne per il fatto che sono archiviate in tempdb. Esistono due tipi di stored procedure temporanee: locali e globali. I due tipi differiscono per i nomi, la visibilità e la disponibilità. Le stored procedure temporanee locali contengono un solo simbolo di cancelletto (#) come primo carattere del nome, sono visibili solo durante la connessione utente corrente e vengono eliminate alla chiusura della connessione. Le stored procedure temporanee globali contengono due simboli di cancelletto (##) come primi caratteri del nome, sono visibili per tutti gli utenti in seguito alla creazione e vengono eliminate al termine dell'ultima sessione che utilizza la stored procedure.

System

Le stored procedure di sistema sono incluse nel motore di database. Sono archiviate fisicamente nel database Resource, interno e nascosto, e a livello logico compaiono nello schema sys di tutti i database definiti dal sistema e di quelli definiti dall'utente. Inoltre, il database msdb contiene anche stored procedure di sistema nello schema dbo che vengono utilizzate per la pianificazione di avvisi e processi. Dal momento che le stored procedure di sistema iniziano con il prefisso sp_, si consiglia di non utilizzare questo prefisso per l'assegnazione di un nome alle procedure definite dall'utente. Per un elenco completo delle stored procedure di sistema, vedere Stored procedure di sistema (Transact-SQL)

SQL Server supporta le stored procedure di sistema che forniscono ai programmi esterni un'interfaccia di SQL Server per varie attività di manutenzione. Queste stored procedure estese utilizzano il prefisso xp_. Per un elenco completo delle stored procedure estese, vedere Stored procedure estese generali (Transact-SQL).

Estese definite dall'utente

Le stored procedure estese permettono di creare routine esterne in un linguaggio di programmazione come C e sono DLL che possono essere caricate ed eseguite in modo dinamico da un'istanza di SQL Server.

Attività correlate

Contenuto correlato

• Stored procedure CLR
• Risoluzione dei nomi posticipata