CREATE FUNCTION (Transact-SQL)

  • Articolo

Data aggiornamento: 14 aprile 2006

Crea una funzione definita dall'utente. Si tratta di una routine Transact-SQL memorizzata o una routine CLR (Common Language Runtime) che restituisce un valore. Non è possibile utilizzare funzioni definite dall'utente per eseguire azioni che modificano lo stato del database. In modo analogo alle funzioni di sistema, le funzioni definite dall'utente possono essere richiamate da una query, mentre le funzioni scalari possono essere eseguite utilizzando un'istruzione EXECUTE come stored procedure.

Le funzioni definite dall'utente vengono modificate tramite l'istruzione ALTER FUNCTION ed eliminate tramite l'istruzione DROP FUNCTION.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function Options
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions
<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
        [ <table_constraint> ] [ ,...n ]
) 

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
            ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

Argomenti

  • schema_name
    Nome dello schema a cui appartiene la funzione definita dall'utente.

  • function_name
    Nome della funzione definita dall'utente. I nomi di funzione devono essere conformi alle regole per gli identificatori ed essere univoci all'interno del database e rispetto al relativo schema.

    [!NOTA] È necessario inserire le parentesi dopo il nome della funzione anche se non viene specificato alcun parametro.

  • **@**parameter_name
    Parametro della funzione definita dall'utente. È possibile dichiarare uno o più parametri.

    Una funzione può avere al massimo 1.024 parametri. Il valore di ciascun parametro dichiarato deve essere specificato dall'utente quando viene eseguita la funzione, a meno che non venga definito un valore predefinito per tale parametro.

    Specificare un nome di parametro utilizzando come primo carattere il simbolo di chiocciola (@). I nomi di parametro devono essere conformi alle regole per gli identificatori. I parametri sono locali rispetto alla funzione. È pertanto possibile utilizzare gli stessi nomi di parametro in altre funzioni. I parametri possono rappresentare solo costanti, non nomi di tabella, di colonna o di altri oggetti del database.

    [!NOTA] ANSI_WARNINGS non viene applicata quando vengono passati parametri a 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 ha esito positivo.

  • [ type_schema_name**.** ] parameter_data_type
    Tipo di dati del parametro e, facoltativamente, lo schema a cui appartiene. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image e timestamp. Non è possibile specificare i tipi non scalari cursor e table come tipo di dati del parametro nelle funzioni Transact-SQL o CLR.

    Se type_schema_name viene omesso, Motore di database di SQL Server 2005 ricerca scalar_parameter_data_type in base all'ordine seguente:

    • Schema contenente i nomi dei tipi di dati di sistema di SQL Server.
    • Schema predefinito dell'utente corrente nel database corrente.
    • Schema dbo nel database corrente.

  • [ **=**default ]
    Valore predefinito per il parametro. Se un valore viene impostato su default, è possibile eseguire la funzione senza specificare valori per il parametro.

    [!NOTA] È possibile specificare valori predefiniti per i parametri delle funzioni CLR ad eccezione dei tipi di dati varchar(max) e varbinary(max).

    Se a un parametro della funzione è associato un valore predefinito, alla chiamata della funzione è necessario specificare la parola chiave DEFAULT per recuperare il valore predefinito. Questa funzionalità risulta diversa dall'utilizzo di parametri con valore predefinito nelle stored procedure in cui l'omissione del parametro implica l'utilizzo del valore predefinito.

  • return_data_type
    Valore restituito di una funzione scalare definita dall'utente. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image e timestamp. Non è possibile specificare i tipi non scalari cursor e table come tipo di dati restituito nelle funzioni Transact-SQL o CLR.

  • function_body
    Specifica che il valore della funzione è definito da una serie di istruzioni Transact-SQL, le quali non hanno alcun effetto di rilievo, ad esempio la modifica di una tabella. function_body è utilizzato solo in funzioni scalari e in funzioni a istruzioni multiple valutate a livello di tabella.

    Nelle funzioni scalari function_body corrisponde a una serie di istruzioni Transact-SQL che in combinazione restituiscono un valore scalare.

    Nelle funzioni a istruzioni multiple valutate a livello di tabella function_body corrisponde a una serie di istruzioni Transact-SQL che popolano una variabile TABLE restituita.

  • scalar_expression
    Specifica il valore scalare restituito dalla funzione scalare.

  • TABLE
    Specifica che il valore restituito della funzione valutata a livello di tabella è una tabella. Alle funzioni valutate a livello di tabella è possibile passare solo vincoli e variabili **@**local_variables.

    Nelle funzioni inline valutate a livello di tabella il valore restituito TABLE viene definito tramite una sola istruzione SELECT. Alle funzioni inline non sono associate variabili restituite.

    Nelle funzioni a istruzioni multiple valutate a livello di tabella **@**return_variable è una variabile TABLE utilizzata per l'archiviazione e l'accumulo delle righe da restituire come valore della funzione. È possibile specificare **@**return_variable solo per le funzioni Transact-SQL e non per le funzioni CLR.

  • select_stmt
    Istruzione SELECT che definisce il valore restituito di una funzione inline valutata a livello di tabella.

  • EXTERNAL NAME <method_specifier>, assembly_name.class_name.method_name
    Specifica il metodo di associazione tra un assembly e la funzione. assembly_name deve corrispondere a un assembly esistente in SQL Server nel database corrente con la visibilità attivata. class_name deve essere un identificatore SQL Server valido, nonché esistere come classe nell'assembly. Se la classe dispone di un nome qualificato a livello di spazio dei nomi che utilizza un punto (.) per separare le varie parti dello spazio dei nomi, il nome della classe deve essere delimitato da parentesi quadre ([]) oppure da virgolette (""). method_name deve essere un identificatore SQL Server valido, nonché esistere come metodo statico nella classe specificata.

    [!NOTA] Per impostazione predefinita, SQL Server non può eseguire il codice CLR. È possibile creare, modificare ed eliminare gli oggetti di database che fanno riferimento a moduli CLR. È tuttavia possibile eseguire questi riferimenti in SQL Server solo dopo aver attivato l'opzione clr enabled. Per abilitare questa opzione, utilizzare sp_configure.

  • <table_type_definition>, ( { <column_definition> <column_constraint> ,   | <computed_column_definition> } ,   [ <table_constraint> ] [ ,...n ], ) ,
    Definisce il tipo di dati della tabella per una funzione Transact-SQL. La dichiarazione di tabella include definizioni di colonna, nonché vincoli di colonna o tabella. La tabella viene sempre inserita nel filegroup primario.
  • < clr_table_type_definition > , ( { column_namedata_type } [ ,...n ] ),
    Definisce i tipi di dati della tabella per una funzione CLR. La dichiarazione di tabella include solo nomi di colonna e tipi di dati. La tabella viene sempre inserita nel filegroup primario.

<function_option>::= e <clr_function_option>::=

Specifica che la funzione avrà una o più opzioni seguenti.

  • ENCRYPTION
    Indica che il testo originale dell'istruzione CREATE FUNCTION verrà convertito da Motore di database in un formato offuscato. L'output dell'offuscamento non è visibile direttamente nelle viste del catalogo in SQL Server 2005. Gli utenti che non hanno accesso a tabelle di sistema o file del database non possono recuperare il testo offuscato. Il testo, tuttavia, sarà disponibile per gli utenti con privilegi di accesso a tabelle di sistema attraverso la porta DAC oppure di accesso diretto a file del database. Gli utenti in grado di collegare un debugger al processo del server possono inoltre recuperare la procedura originale dalla memoria in fase di esecuzione. Per ulteriori informazioni sull'accesso ai metadati di sistema, vedere Configurazione della visibilità dei metadati.

    Tramite questa opzione è possibile evitare la pubblicazione della funzione come parte della replica di SQL Server. Questa opzione non può essere specificata per le funzioni CLR.

  • SCHEMABINDING
    Specifica che la funzione è associata agli oggetti di database a cui fa riferimento. Questa condizione impedisce la modifica della funzione se altri oggetti associati allo schema vi fanno riferimento.

    L'associazione della funzione agli oggetti cui fa riferimento viene rimossa solo quando viene eseguita una delle azioni seguenti:

    • La funzione viene eliminata.
    • La funzione viene modificata tramite l'istruzione ALTER senza specificare l'opzione SCHEMABINDING.

    Una funzione può essere associata a uno schema solo se vengono soddisfatte le condizioni seguenti:

    • Si tratta di una funzione Transact-SQL.
    • Le funzioni definite dall'utente e le viste a cui la funzione fa riferimento sono anch'esse associate a uno schema.
    • Per i riferimenti agli oggetti, nella funzione vengono utilizzati nomi composti da due parti.
    • La funzione e gli oggetti a cui fa riferimento appartengono allo stesso database.
    • L'utente che ha eseguito l'istruzione CREATE FUNCTION dispone dell'autorizzazione REFERENCES per gli oggetti del database a cui la funzione fa riferimento.

    Non è possibile specificare l'opzione SCHEMABINDING per le funzioni CLR o per le funzioni che fanno riferimenti a tipi di dati alias.

  • RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
    Specifica l'attributo OnNULLCall di una funzione con valori scalari. Se omesso, viene utilizzata l'opzione CALLED ON NULL INPUT per impostazione predefinita. Ciò significa che viene eseguito il corpo della funzione anche se come argomento viene passato NULL.

    Se in una funzione CLR si specifica l'opzione RETURNS NULL ON NULL INPUT, SQL Server restituisce NULL se uno qualsiasi degli argomenti ricevuti è NULL senza effettivamente richiamare il corpo della funzione. Se per il metodo di una funzione CLR specificato in <method_specifier> è stato definito un attributo personalizzato impostato su RETURNS NULL ON NULL INPUT, ma l'istruzione CREATE FUNCTION include CALLED ON NULL INPUT, l'istruzione CREATE FUNCTION risulta prioritaria. Non è possibile specificare l'attributo OnNULLCall per le funzioni CLR valutata a livello di tabella.

  • Clausola EXECUTE AS
    Specifica il contesto di protezione nel quale viene eseguita la funzione definita dall'utente. Sarà pertanto possibile controllare l'account utente utilizzato da SQL Server per convalidare le autorizzazioni per qualsiasi oggetto di database a cui la funzione fa riferimento.

    [!NOTA] Non è possibile specificare la clausola EXECUTE AS per le funzioni inline definite dall'utente.

    Per ulteriori informazioni, vedere Clausola EXECUTE AS (Transact-SQL).

< column_definition >::=

Definisce il tipo di dati della tabella. La dichiarazione di tabella include definizioni di colonna e vincoli. Per le funzioni CLR è possibile specificare solo column_name e data_type.

  • column_name
    Nome di una colonna della tabella. I nomi di colonna devono essere conformi alle regole per gli identificatori ed essere univoci nella tabella. column_name può essere composto da 1 a 128 caratteri.
  • data_type
    Specifica il tipo di dati della colonna. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image, char, varchar, varchar(max) e timestamp. Non è possibile specificare il tipo non scalare cursor come tipo di dati della colonna nelle funzioni Transact-SQL o CLR.
  • DEFAULT constant_expression
    Specifica il valore definito per la colonna quando un valore non viene specificato in modo esplicito durante un inserimento. constant_expression è una costante, NULL oppure un valore della funzione di sistema. Le definizioni dell'opzione DEFAULT possono essere applicate a qualsiasi colonna eccetto quelle che includono la proprietà IDENTITY. Non è possibile specificare l'opzione DEFAULT per le funzioni CLR valutate a livello di tabella.

  • COLLATE collation_name
    Specifica le regole di confronto per la colonna. Se omesso, alla colonna vengono assegnate le regole di confronto predefinite del database. È possibile utilizzare nomi di regole di confronto di Windows o SQL. Per ulteriori informazioni sulle regole di confronto e per un elenco di tali regole, vedere Windows_collation_name (Transact-SQL) e SQL_collation_name (Transact-SQL).

    La clausola COLLATE consente di modificare le regole di confronto solo delle colonne di tipo char, varchar, nchar e nvarchar.

    Non è possibile specificare l'opzione COLLATE per le funzioni CLR valutate a livello di tabella.

  • ROWGUIDCOL
    Indica che la nuova colonna è un identificatore univoco di riga globale. È possibile designare come colonna ROWGUIDCOL una sola colonna di tipo uniqueidentifier per ogni tabella. La proprietà ROWGUIDCOL può essere assegnata solo a una colonna uniqueidentifier.

    La proprietà ROWGUIDCOL non impone l'univocità dei valori archiviati nella colonna e non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, utilizzare la funzione NEWID nelle istruzioni INSERT. È possibile specificare un valore predefinito. Non è tuttavia possibile specificare l'opzione NEWID come valore predefinito.

  • IDENTITY
    Indica che la nuova colonna è una colonna Identity. Quando si aggiunge una nuova riga alla tabella, SQL Server assegna alla colonna un valore univoco e incrementale. Le colonne Identity vengono in genere utilizzate in combinazione con vincoli PRIMARY KEY e svolgono la funzione di identificatore di riga univoco per la tabella. La proprietà IDENTITY può essere assegnata alle colonne tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Ogni tabella può includere una sola colonna Identity. Non è possibile associare valori predefiniti e vincoli DEFAULT alle colonne Identity. È necessario specificare sia il valore per seed che il valore per increment oppure nessuno dei due valori. In questo secondo caso, il valore predefinito è (1,1).

    Non è possibile specificare l'opzione IDENTITY per le funzioni CLR valutate a livello di tabella.

    • seed
      Valore integer da assegnare alla prima riga della tabella.
    • increment
      Valore integer da aggiungere al valore seed per le righe successive della tabella.

< column_constraint >::= e < table_constraint>::=

Definisce il vincolo per una colonna o tabella specificata. Per le funzioni CLR l'unico tipo di vincolo consentito è NULL. I vincoli denominati non sono consentiti.

  • NULL | NOT NULL
    Determina se i valori NULL sono supportati nella colonna. L'opzione NULL non è esattamente un vincolo, ma può essere specificata allo stesso modo di NOT NULL. Non è possibile specificare l'opzione NOT NULL per le funzioni CLR valutate a livello di tabella.
  • PRIMARY KEY
    Vincolo che impone l'integrità di entità per una colonna specificata tramite un indice univoco. Nelle funzioni definite dall'utente valutate a livello di tabella il vincolo PRIMARY KEY può essere creato solo su una colonna per tabella. Non è possibile specificare il vincolo PRIMARY KEY per le funzioni CLR valutate a livello di tabella.
  • UNIQUE
    Vincolo che impone l'integrità di entità per una o più colonne specificate tramite un indice univoco. Una tabella può includere più vincoli UNIQUE. Non è possibile specificare il vincolo UNIQUE per le funzioni CLR valutate a livello di tabella.

  • CLUSTERED | NONCLUSTERED
    Definisce la creazione di un indice cluster o non cluster per il vincolo PRIMARY KEY o UNIQUE. I vincoli PRIMARY KEY utilizzano l'opzione CLUSTERED, mentre i vincoli UNIQUE utilizzano l'opzione NONCLUSTERED.

    L'opzione CLUSTERED può essere specificata solo per un vincolo. Se per un vincolo UNIQUE si specifica CLUSTERED e viene specificato anche un vincolo PRIMARY KEY, il vincolo PRIMARY KEY utilizza l'opzione NONCLUSTERED.

    Non è possibile specificare le opzioni CLUSTERED e NON CLUSTERED per le funzioni CLR valutate a livello di tabella.

  • CHECK
    Vincolo che impone l'integrità di dominio tramite la limitazione dei valori che è possibile inserire in una o più colonne. Non è possibile specificare i vincoli CHECK per le funzioni CLR valutate a livello di tabella.

    • logical_expression
      Espressione logica che restituisce TRUE o FALSE.

<computed_column_definition>::=

Specifica una colonna calcolata. Per ulteriori informazioni sulle colonne calcolate, vedere CREATE TABLE (Transact-SQL).

  • column_name
    Nome della colonna calcolata.
  • computed_column_expression
    Espressione che definisce il valore di una colonna calcolata.

<index_option>::=

Specifica le opzioni per l'indice PRIMARY KEY o UNIQUE. Per ulteriori informazioni sulle opzioni per gli indici, vedere CREATE INDEX (Transact-SQL).

  • PAD_INDEX = { ON | OFF }
    Specifica il riempimento dell'indice. Il valore predefinito è OFF.
  • FILLFACTOR = fillfactor
    Specifica una percentuale indicante il livello di riempimento del livello foglia di ogni pagina di indice applicato da Motore di database durante la creazione o la modifica dell'indice. fillfactor deve essere un valore integer compreso tra 1 e 100. Il valore predefinito è 0.
  • IGNORE_DUP_KEY = { ON | OFF }
    Specifica l'errore restituito per valori di chiave duplicati in una transazione di inserimento di più righe in un indice cluster univoco o in un indice non cluster univoco. Il valore predefinito è OFF.
  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Specifica se vengono ricalcolate le statistiche di distribuzione. Il valore predefinito è OFF.
  • ALLOW_ROW_LOCKS = { ON | OFF }
    Specifica se sono consentiti blocchi a livello di riga. Il valore predefinito è ON.
  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Specifica se sono consentiti blocchi a livello di pagina. Il valore predefinito è ON.

Osservazioni

Le funzioni definite dall'utente sono funzioni con valori scalari o funzioni valutate a livello di tabella. Le funzioni con valori scalari sono funzioni in cui nella clausola RETURNS è specificato uno dei tipi di dati scalari. È possibile definire queste funzioni tramite più istruzioni Transact-SQL.

Le funzioni vengono valutate a livello di tabella se nella clausola RETURNS è specificato TABLE. Queste funzioni sono classificate come funzioni inline o a più istruzioni a seconda della definizione del corpo della funzione. Per ulteriori informazioni, vedere Funzioni definite dall'utente valutate a livello di tabella.

In una funzione le istruzioni seguenti sono valide:

  • Istruzioni di assegnazione.
  • Istruzioni per il controllo di flusso, escluse le istruzioni TRY...CATCH.
  • Istruzioni DECLARE che definiscono le variabili dati locali e i cursori locali.
  • Istruzioni SELECT contenenti gli elenchi di selezione con espressioni che assegnano valori alle variabili locali.
  • Operazioni di cursore che fanno riferimento a cursori locali dichiarati, aperti, chiusi e deallocati nella funzione. Sono consentite solo istruzioni FETCH che assegnano valori alle variabili locali tramite la clausola INTO. Non sono consentite istruzioni FETCH che restituiscono dati al client.
  • Istruzioni INSERT, UPDATE e DELETE che modificano le variabili table locali.
  • Istruzioni EXECUTE che richiamano stored procedure estese.
  • Per ulteriori informazioni, vedere Creazione di funzioni definite dall'utente (Motore di database).

Nidificazione delle funzioni definite dall'utente

È possibile nidificare le funzioni definite dall'utente, ovvero una funzione definita dall'utente ne può richiamare un'altra. Il livello di nidificazione aumenta all'avvio della funzione richiamata e diminuisce al termine dell'esecuzione della funzione. Le funzioni definite dall'utente possono essere nidificate fino a un massimo di 32 livelli. Se viene superato il livello massimo di nidificazioni, l'intera sequenza di funzioni chiamanti ha esito negativo.

[!NOTA]    Qualsiasi riferimento al codice gestito all'interno di una funzione Transact-SQL definita dall'utente viene considerato come un livello nel contesto del limite di 32 livelli di nidificazione. I metodi richiamati da codice gestito non vengono inclusi nel conteggio per questo limite.

Proprietà delle funzioni

Nelle versioni precedenti di SQL Server le funzioni vengono classificate solo come di tipo deterministico o non deterministico. In SQL Server 2005 le funzioni dispongono delle proprietà descritte di seguito. I valori di tali proprietà determinano se le funzioni sono utilizzabili nelle colonne calcolate che possono essere persistenti o indicizzate.

Proprietà Descrizione Note

IsDeterministic

La funzione è deterministica o non deterministica.

L'accesso ai dati locali è consentito nelle funzioni deterministiche. Ad esempio, le funzioni che restituiscono sempre lo stesso valore ogni volta che vengono richiamate utilizzando un set specifico di valori di input e con lo stesso stato del database vengono definite funzioni deterministiche.

IsPrecise

La funzione è precisa o imprecisa.

Le funzioni imprecise includono operazioni quali le operazioni a virgola mobile.

IsSystemVerified

Le proprietà relative alla precisione e le proprietà deterministiche della funzione possono essere verificate tramite SQL Server.

 

SystemDataAccess

La funzione accede ai dati di sistema (cataloghi di sistema o tabelle di sistema virtuali) nell'istanza locale di SQL Server.

 

UserDataAccess

La funzione accede ai dati utente nell'istanza locale di SQL Server.

Include le tabelle definite dall'utente e le tabelle temporanee, ma non le variabili di tabella.

Le proprietà relative alla precisione e le proprietà deterministiche delle funzioni Transact-SQL vengono definite automaticamente da SQL Server. Per ulteriori informazioni, vedere Linee guida per la progettazione di funzioni definite dall'utente. Le proprietà relative all'accesso ai dati e le proprietà deterministiche delle funzioni CLR possono essere specificate dall'utente. Per ulteriori informazioni, vedere Overview of CLR Integration Custom Attributes.

Per visualizzare i valori correnti di queste proprietà, utilizzare OBJECTPROPERTYEX.

Indicizzazione di colonne calcolate che richiamano una funzione definita dall'utente

È possibile utilizzare una colonna calcolata che richiama una funzione definita dall'utente in un indice se per le proprietà della funzione definita dall'utente sono stati impostati i valori seguenti:

  • IsDeterministic = true
  • IsSystemVerified = true (a meno che la colonna calcolata non sia persistente)
  • UserDataAccess = false
  • SystemDataAccess = false

Per ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

Chiamata di stored procedure estese da funzioni

Una stored procedure estesa, quando viene richiamata dall'interno di una funzione, non può restituire set di risultati al client. Le API ODS che restituiscono set di risultati al client restituiscono FAIL. La stored procedure estesa può riconnettersi a un'istanza di SQL Server, ma è necessario che non partecipi alla stessa transazione della funzione che l'ha richiamata.

In modo analogo a una chiamata eseguita dall'interno di un batch o di una stored procedure, la stored procedure estesa viene eseguita nel contesto dell'account di protezione di Windows in base a cui SQL Server è in esecuzione. È importante che il proprietario della stored procedure prenda in considerazione questo fattore quando concede l'autorizzazione EXECUTE agli utenti.

Chiamata di funzione

È possibile richiamare funzioni con valori scalari laddove vengono utilizzate espressioni scalari. Sono incluse le colonne calcolate e le definizioni dei vincoli CHECK. Le funzioni con valori scalari possono essere eseguite anche tramite l'utilizzo dell'istruzione EXECUTE. È necessario richiamare funzioni con valori scalari utilizzando almeno il nome in due parti della funzione. Per ulteriori informazioni sui nomi composti da più parti, vedere Convenzioni della sintassi Transact-SQL (Transact-SQL). Le funzioni valutate a livello di tabella possono essere richiamate dove sono consentite le espressioni di tabella nella clausola FROM delle istruzioni SELECT, INSERT, UPDATE o DELETE. Per ulteriori informazioni, vedere Esecuzione di funzioni definite dall'utente (Motore di database).

Utilizzo di parametri e valori restituiti nelle funzioni CLR

Se si specificano parametri in una funzione CLR, essi dovranno essere di tipo SQL Server come precedentemente definito per scalar_parameter_data_type. Per ulteriori informazioni sul confronto tra i tipi di dati di sistema di SQL Server e i tipi di dati di integrazione CLR oppure i tipi di dati CLR di .NET Framework, vedere SQL Server Data Types and Their .NET Framework Equivalents.

Affinché SQL Server faccia riferimento al metodo corretto in caso di un sovraccarico a livello di classe, è necessario che il metodo specificato in <method_specifier>:

  • Riceva lo stesso numero di parametri specificati in [ ,...n ].
  • Riceva tutti i parametri in base al valore e non in base al riferimento.
  • Utilizzi tipi di parametro compatibili con quelli specificati nella funzione SQL Server.

Se per il tipo di dati restituito della funzione CLR viene specificato un tipo tabella (RETURNS TABLE), il tipo di dati restituito del metodo specificato in <method_specifier> deve essere IEnumerator o IEnumerable. Si presuppone che l'interfaccia venga implementata dall'autore della funzione. A differenza delle funzioni Transact-SQL, le funzioni CLR non possono includere vincoli PRIMARY KEY, UNIQUE o CHECK in <table_type_definition>. I tipi di dati delle colonne specificati in <table_type_definition> devono corrispondere ai tipi delle colonne corrispondenti del set di risultati restituito dal metodo specificato in <method_specifier> in fase di esecuzione. Questa verifica del tipo non viene eseguita al momento della creazione della funzione.

Per ulteriori informazioni sulla programmazione delle funzioni CLR, vedere CLR User-Defined Functions.

Istruzioni SQL non consentite

Nella definizione di una funzione Transact-SQL definita dall'utente non è possibile includere le istruzioni di Service Broker seguenti:

  • BEGIN DIALOG CONVERSATION
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • MOVE CONVERSATION
  • RECEIVE
  • SEND

Visualizzazione delle informazioni sulle funzioni

Per visualizzare la definizione delle funzioni Transact-SQL definite dall'utente, utilizzare la vista del catalogo sys.sql_modules nel database in cui esiste la funzione.

Ad esempio:

USE AdventureWorks;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO

[!NOTA] La definizione delle funzioni create tramite l'opzione ENCRYPTION non possono essere visualizzate mediante la vista del catalogo sys.sql_modules. Vengono tuttavia visualizzate altre informazioni sulle funzioni crittografate.

Per visualizzare le informazioni relative alle funzioni CLR definite dall'utente, utilizzare la vista del catalogo sys.assembly_modules nel database in cui esiste la funzione.

Per visualizzare le informazioni sui parametri definiti nelle funzioni definite dall'utente, utilizzare la vista del catalogo sys.parameters nel database in cui esiste la funzione.

Per visualizzare un report degli oggetti a cui fa riferimento una funzione, utilizzare sys.sql_dependencies.

Autorizzazioni

È necessario disporre dell'autorizzazione CREATE FUNCTION nel database e dell'autorizzazione ALTER per lo schema in cui la funzione è in fase di creazione. Se per la funzione viene specificato un tipo definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per tale tipo.

Esempi

A. Utilizzo di una funzione definita dall'utente con valori scalari per il calcolo della settimana ISO

Nell'esempio seguente viene creata la funzione definita dall'utente ISOweek. Questa funzione calcola il numero di settimana ISO in base a un argomento di data specificato. Per consentire alla funzione di eseguire il calcolo correttamente, è necessario richiamare SET DATEFIRST 1 prima della funzione.

Nell'esempio viene inoltre illustrato l'utilizzo della clausola EXECUTE AS per specificare il contesto di protezione in cui è possibile eseguire una stored procedure. Nell'esempio l'opzione CALLER specifica che la procedura verrà eseguita nel contesto dell'utente che l'ha richiamata. Le altre opzioni che è possibile specificare sono SELF, OWNER e user_name.

Di seguito è riportata la chiamata della funzione, in cui DATEFIRST è impostato su 1.

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

Set di risultati:

ISO Week
----------------
52

B. Creazione di una funzione inline valutata a livello di tabella

Nell'esempio seguente viene restituita una funzione inline valutata a livello di tabella. Vengono restituite tre colonne ProductID, Name e il valore aggregato dei totali dell'anno in corso per negozio come YTD Total per ogni prodotto venduto al negozio.

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Per richiamare la funzione, eseguire la query seguente.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Creazione di una funzione a istruzioni multiple valutata a livello di tabella

Nell'esempio seguente viene creata la funzione valutata a livello di tabella fn_FindReports(InEmpID). Se si specifica un ID dipendente valido, la funzione restituisce una tabella che include tutti i dipendenti che hanno rapporti diretti o indiretti con il dipendente specificato. La funzione utilizza un'espressione di tabella comune ricorsiva (CTE, Common Table Expression) per restituire l'elenco gerarchico dei dipendenti. Per ulteriori informazioni sulle espressioni CTE ricorsive, vedere WITH common_table_expression (Transact-SQL).

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

D. Creazione di una funzione CLR

Nell'esempio seguente si presume che gli esempi del Motore di database di SQL Server siano installati nella posizione predefinita del computer locale e che l'applicazione di esempio StringManipulate.csproj sia compilata. Per ulteriori informazioni, vedere Manipolazione di stringhe contenenti caratteri aggiuntivi.

Nell'esempio viene creata la funzione CLR len_s, ma prima che venga effettivamente creata la funzione, l'assembly SurrogateStringFunction.dll viene registrato nel database locale.

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Per un esempio relativo alla creazione di una funzione CLR valutata a livello di tabella, vedere CLR Table-Valued Functions.

Vedere anche

Riferimento

ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)

Altre risorse

Funzioni definite dall'utente (Motore di database)
CLR User-Defined Functions

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione Cronologia

14 aprile 2006
Nuovo contenuto:
  • Aggiunta di informazioni sulla necessità di richiamare funzioni con valori scalari utilizzando almeno il nome in due parti della funzione in "Chiamata di funzione" nella sezione Osservazioni.

5 dicembre 2005
Contenuto modificato
  • Chiarimento della definizione dell'opzione ENCRYPTION.