Share via

sp_describe_undeclared_parameters (Transact-SQL)

  • Articolo

Restituisce un set di risultati che contiene metadati sui parametri non dichiarati in un batch Transact-SQL. Considera ogni parametro utilizzato nel batch @tsql, ma non dichiarato in @ params. Viene restituito un set di risultati che contiene una riga per ognuno di questi parametri, con le informazioni sul tipo dedotte per quel parametro. La procedura restituisce un set di risultati vuoto se il batch di input @tsql non contiene parametri, a eccezione di quelli dichiarati in @params.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

sp_describe_undeclared_parameters 
    [ @tsql = ] 'Transact-SQL_batch' 
    [ , [ @params = ] N'parameters' data type ] [, ...n]

Argomenti

  • [ @tsql = ] 'Transact-SQL_batch'
    Una o più istruzioni Transact-SQL. Transact-SQL_batch può essere nvarchar(n) o nvarchar(max).

  • [ @params = ] N'parameters'
    Tramite @params viene fornita una stringa di dichiarazione per i parametri del batch Transact-SQL, analogamente al modo in cui funziona sp_executesql. Parameters può essere nvarchar(n) o nvarchar(max).

    Stringa contenente le definizioni di tutti i parametri incorporati in Transact-SQL_batch. La stringa deve essere una costante o una variabile Unicode. Ogni definizione di parametro è costituita da un nome del parametro e da un tipo di dati. n è un segnaposto che indica definizioni di parametro aggiuntive. Se l'istruzione Transact-SQL o il batch nell'istruzione non contiene parametri, @params non è necessario. Il valore predefinito per questo parametro è NULL.

  • Datatype
    Tipo di dati del parametro.

Valori restituiti

sp_describe_undeclared_parameters restituisce sempre zero in caso di esito positivo. Se la procedura viene chiamata come RPC e viene generato un errore, lo stato restituito viene popolato dal tipo di errore, come descritto nella colonna error_type di sys.dm_exec_describe_first_result_set. Se la procedura viene chiamata da Transact-SQL, il valore restituito è sempre zero, anche in caso di errore.

Set di risultati

sp_describe_undeclared_parameters restituisce il set di risultati seguente.

Nome colonna

Tipo di dati

Descrizione

parameter_ordinal

int NOT NULL

Contiene la posizione ordinale del parametro nel set di risultati. La posizione del primo parametro viene specificata come 1.

name

sysname NOT NULL

Contiene il nome del parametro.

suggested_system_type_id

int NOT NULL

Contiene system_type_id del tipo di dati del parametro, come specificato in sys.types.

Per i tipi CLR, anche se la colonna system_type_name restituisce NULL, questa colonna restituisce il valore 240.

suggested_system_type_name

nvarchar (256) NULL

Contiene il nome del tipo di dati. Include gli argomenti, quali lunghezza, precisione e scala, specificati per il tipo di dati del parametro. Se il tipo di dati è un tipo di alias definito dall'utente, il tipo di sistema sottostante viene specificato qui. Se il tipo di dati è un tipo CLR definito dall'utente, in questa colonna viene restituito NULL. Se non è possibile dedurre il tipo del parametro, viene restituito NULL.

suggested_max_length

smallint NOT NULL

Vedere sys.columns per la descrizione della colonna max_length.

suggested_precision

tinyint NOT NULL

Vedere sys.columns per la descrizione della colonna PRECISION.

suggested_scale

tinyint NOT NULL

Vedere sys.columns per la descrizione della colonna SCALE.

suggested_user_type_id

int NULL

Per i tipi di alias e CLR, contiene il valore user_type_id del tipo di dati della colonna, come specificato in sys.types. In caso contrario, è NULL.

suggested_user_type_database

sysname NULL

Per i tipi di alias e CLR, contiene il nome del database in cui è definito il tipo. In caso contrario, è NULL.

suggested_user_type_schema

sysname NULL

Per i tipi di alias e CLR, contiene il nome dello schema in cui è definito il tipo. In caso contrario, è NULL.

suggested_user_type_name

sysname NULL

Per i tipi di alias e CLR, contiene il nome del tipo. In caso contrario, è NULL.

suggested_assembly_qualified_type_name

nvarchar (4000) NULL

Per i tipi CLR, restituisce il nome dell'assembly e la classe che definisce il tipo. In caso contrario, è NULL.

suggested_xml_collection_id

int NULL

Contiene il valore xml_collection_id del tipo di dati del parametro, come specificato in sys.columns. Questa colonna restituisce NULL se il tipo restituito non è associato a una raccolta XML Schema.

suggested_xml_collection_database

sysname NULL

Contiene il database in cui viene definita la raccolta XML Schema associata a questo tipo. Questa colonna restituisce NULL se il tipo restituito non è associato a una raccolta XML Schema.

suggested_xml_collection_schema

sysname NULL

Contiene lo schema in cui viene definita la raccolta XML Schema associata a questo tipo. Questa colonna restituisce NULL se il tipo restituito non è associato a una raccolta XML Schema.

suggested_xml_collection_name

sysname NULL

Contiene il nome della raccolta XML Schema associata a questo tipo. Questa colonna restituisce NULL se il tipo restituito non è associato a una raccolta XML Schema.

suggested_is_xml_document

bit NOT NULL

Restituisce 1 se il tipo restituito è XML ed è garantito che il tipo sia un documento XML. In caso contrario, restituisce 0.

suggested_is_case_sensitive

bit NOT NULL

Restituisce 1 se la colonna è di un tipo string che fa distinzione tra maiuscole e minuscole e 0 in caso contrario.

suggested_is_fixed_length_clr_type

bit NOT NULL

Restituisce 1 se la colonna è di un tipo CLR a lunghezza fissa e 0 in caso contrario.

suggested_is_input

bit NOT NULL

Restituisce 1 se il parametro viene utilizzato in qualsiasi posizione, a eccezione del lato sinistro di un'assegnazione. In caso contrario, restituisce 0.

suggested_is_output

bit NOT NULL

Restituisce 1 se il parametro viene utilizzato sul lato sinistro di un'assegnazione o se viene passato a un parametro di output di una stored procedure. In caso contrario, restituisce 0.

formal_parameter_name

sysname NULL

Se il parametro è un argomento per una stored procedure o una funzione definita dall'utente, restituisce il nome del parametro formale corrispondente. In caso contrario, restituisce NULL.

suggested_tds_type_id

int NOT NULL

Per uso interno.

suggested_tds_length

int NOT NULL

Per uso interno.

Osservazioni

sp_describe_undeclared_parameters restituisce sempre uno stato pari a zero.

Lo scenario più comune si verifica quando a un'applicazione viene fornita un'istruzione Transact-SQL che potrebbe contenere parametri e li deve elaborare in qualche modo. Un esempio è dato da un'interfaccia utente quale ODBCTest o RowsetViewer in cui l'utente fornisce una query con la sintassi del parametro ODBC. L'applicazione deve individuare in modo dinamico il numero di parametri che verranno richiesti singolarmente all'utente.

Un altro esempio è dato dalla situazione in cui, senza l'input dell'utente, un'applicazione deve eseguire in ciclo i parametri e ottenere i relativi dati da altri percorsi, ad esempio una tabella. In questo caso non è necessario passare immediatamente tutte le informazioni sui parametri, ma è possibile ottenerle dal provider e acquisire i dati dalla tabella. Il codice che utilizza sp_describe_undeclared_parameters è più generico, pertanto è meno probabile che necessiti di modifiche se la struttura dei dati dovesse cambiare in seguito.

sp_describe_undeclared_parameters restituisce un errore nei casi illustrati di seguito.

  • Il batch @tsql di input non è un batch Transact-SQL valido. La validità viene determinata tramite l'analisi del batch Transact-SQL. Qualsiasi errore causato dal batch durante l'ottimizzazione della query o durante l'esecuzione viene ignorato durante la determinazione della validità del batch Transact-SQL.

  • @params non è NULL e contiene una stringa che non è una stringa di dichiarazione sintatticamente valida per i parametri oppure contiene una stringa che dichiara qualsiasi parametro più di una volta.

  • Il batch Transact-SQL di input dichiara una variabile locale avente lo stesso nome di un parametro dichiarato in @params.

  • L'istruzione crea qualsiasi tabella temporanea.

Se @tsql non contiene parametri, a eccezione di quelli dichiarati in @params, la procedura restituisce un set di risultati vuoto.

Algoritmo di selezione dei parametri

Per una query con parametri non dichiarati, la deduzione del tipo di dati per i parametri non dichiarati si svolge in tre passaggi.

Passaggio 1

Il primo passaggio della deduzione del tipo di dati per una query con parametri non dichiarati consiste nel trovare i tipi di dati di tutte le sottoespressioni i cui tipi di dati non dipendono dai parametri non dichiarati. È possibile determinare il tipo per le espressioni seguenti:

  • Colonne, costanti, variabili e parametri dichiarati.

  • Risultati di una chiamata a una funzione definita dall'utente.

  • Espressione con tipi di dati che non dipendono dai parametri non dichiarati per tutti gli input.

Si consideri, ad esempio, la query SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. Contrariamente alle espressioni @p1 e @p2 + 2, le espressioni dbo.tbl(@p1) + c1 e c2 dispongono di tipi di dati.

Dopo questo passaggio, se un'espressione, che non sia una chiamata a una funzione definita dall'utente, dispone di due argomenti senza tipi di dati, si verifica un errore durante la deduzione dei tipi. In tutti gli esempi seguenti si verificano errori:

SELECT * FROM t1 WHERE @p1 = @p2
SELECT * FROM t1 WHERE c1 = @p1 + @p2
SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3)

Nell'esempio seguente non viene generato alcun errore:

SELECT * FROM t1 WHERE @p1 = dbo.tbl(c1, @p2, @p3)

Passaggio 2

Per un determinato parametro non dichiarato @p, l'algoritmo di deduzione dei tipi trova l'espressione più interna E(@p) che contiene @p ed è uno dei seguenti:

  • Argomento per un operatore di confronto o assegnazione.

  • Argomento per una funzione definita dall'utente, incluse funzioni definite dall'utente con valori di tabella, procedura o metodo.

  • Argomento per una clausola VALUES di un'istruzione INSERT.

  • Argomento per CAST o CONVERT.

L'algoritmo di deduzione dei tipi trova un tipo di dati di destinazione TT(@p per E(@p). I tipi di dati di destinazione per gli esempi precedenti sono i seguenti:

  • Tipo di dati dell'altro lato dell'operatore di confronto o assegnazione.

  • Tipo di dati dichiarato del parametro a cui viene passato questo argomento.

  • Tipo di dati della colonna in cui viene inserito questo valore.

  • Tipo di dati nel quale l'istruzione esegue il cast o la conversione.

Si consideri, ad esempio, la query SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) è il tipo di dati restituito dichiarato di dbo.tbl e TT(@p2) è il tipo di dati del parametro dichiarato per dbo.tbl.

Se @p non è contenuto in alcuna espressione elencata all'inizio del passaggio 2, l'algoritmo di deduzione dei tipi determina che E(@p) è l'espressione scalare dalle dimensioni maggiori contenente @p e non calcola un tipo di dati di destinazione TT(@p) per E(@p). Se ad esempio la query è SELECT @p + 2, E(@p) = @p + 2, senza TT(@p).

Passaggio 3

Dopo avere identificato E(@p) e TT(@p), l'algoritmo di deduzione dei tipi deduce un tipo di dati per @p in uno dei modi seguenti:

  • Deduzione semplice

    Se E(@p) = @p e TT(@p) esiste, ovvero se @p è direttamente un argomento per una delle espressioni elencate all'inizio del passaggio 2, l'algoritmo di deduzione dei tipi deduce il tipo di dati di @p come TT(@p). Ad esempio:

    SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3)

    Il tipo di dati per @p1, @p2 e @p3 sarà rispettivamente il tipo di dati di c1, il tipo di dati restituito di dbo.tbl e il tipo di dati del parametro per dbo.tbl.

    In un caso speciale, se @p è un argomento per un operatore <, >, <= o >=, le regole della deduzione semplice non si applicano. L'algoritmo di deduzione dei tipi utilizzerà le regole della deduzione generale illustrate nella sezione successiva. Se ad esempio c1 è una colonna del tipo di dati char(30), si considerino le due query seguenti:

    SELECT * FROM t WHERE c1 = @p
SELECT * FROM t WHERE c1 > @p

    Nel primo caso, l'algoritmo di deduzione dei tipi deduce char(30) come il tipo di dati per @p in base alle regole illustrate in precedenza. Nel secondo caso, l'algoritmo di deduzione dei tipi deduce varchar(8000) in base alle regole della deduzione generale illustrate nella sezione successiva.

  • Deduzione generale

    Se la deduzione semplice non è applicabile, si considerino i tipi di dati seguenti per i parametri non dichiarati:

    • Tipi di dati Integer (bit, tinyint, smallint, int, bigint)

    • Tipi di dati money (smallmoney, money)

    • Tipi di dati a virgola mobile (float, real)

    • numeric(38, 19) - Gli altri tipi di dati numerici o decimali non vengono presi in considerazione.

    • varchar(8000), varchar(max), nvarchar(4000) e nvarchar(max). Altri tipi di dati string quali text, char(8000), nvarchar(30) e così via non vengono presi in considerazione.

    • varbinary(8000) e varbinary(max). Altri tipi di dati binari quali image, binary(8000), varbinary(30) e così via non vengono presi in considerazione.

    • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7). Altri tipi di dati data e ora quale time(4) non vengono presi in considerazione.

    • sql_variant

    • xml

    • Tipi CLR definiti dal sistema (hierarchyid, geometry, geography)

    • Tipi CLR definiti dall'utente

Criteri di selezione

Di tutti i tipi di dati candidati, qualsiasi tipo di dati che renderebbe non valida la query viene rifiutato. Dei tipi di dati candidati rimanenti, l'algoritmo di deduzione dei tipi ne seleziona uno in base alle regole seguenti.

  1. Viene selezionato il tipo di dati che produce il numero più piccolo di conversioni implicite in E(@p). Se un tipo di dati produce un tipo di dati per E(@p) diverso da TT(@p), l'algoritmo di deduzione dei tipi considera questa conversione come una conversione implicita aggiuntiva dal tipo di dati di E(@p) a TT(@p).

    Ad esempio:

    SELECT * FROM t WHERE Col_Int = Col_Int + @p

    In questo caso E(@p) è Col_Int + @p e TT(@p) è int. int viene scelto per @p perché non produce conversioni implicite. Qualsiasi altra scelta del tipo di dati produce almeno una conversione implicita.

  2. Se più tipi di dati hanno un valore equivalente per il numero più piccolo di conversioni, viene utilizzato il tipo di dati con la precedenza maggiore. Ad esempio:

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p

    In questo caso, int e smallint producono una conversione. Ogni altro tipo di dati produce più di una conversione. Poiché int ha la precedenza su smallint, int viene utilizzato per @p. Per ulteriori informazioni sulla precedenza dei tipi di dati, vedere Precedenza dei tipi di dati (Transact-SQL).

    Questa regola è applicabile solo in presenza di una conversione implicita tra ogni tipo di dati con valori equivalenti in base alla regola 1 e il tipo di dati con la precedenza maggiore. In assenza di una conversione implicita, la deduzione dei tipi di dati genera un errore. Ad esempio, nella query SELECT @p FROM t, la deduzione dei tipi di dati ha esito negativo perché qualsiasi tipo di dati per @p sarebbe ugualmente appropriato. Non vi è, ad esempio, conversione implicita da int a xml.

  3. Se due tipi di dati simili hanno valori equivalenti in base alla regola 1, ad esempio varchar(8000) e varchar(max), viene scelto il tipo di dati più piccolo (varchar(8000)). Lo stesso principio è applicabile ai tipi di dati nvarchar e varbinary.

  4. Ai fini della regola 1, l'algoritmo di deduzione dei tipi preferisce alcune conversioni ad altre. Le conversioni dalla migliore alla peggiore sono:

    1. Conversione tra lo stesso tipo di dati di base di lunghezza diversa.

    2. Conversione tra versione a lunghezza fissa e versione a lunghezza variabile degli stessi tipi di dati, ad esempio da char a varchar.

    3. Conversione tra NULL e int.

    4. Qualsiasi altra conversione.

Per la query SELECT * FROM t WHERE [Col_varchar(30)] > @p, viene ad esempio scelto varchar(8000) perché la conversione (a) è la migliore. Per la query SELECT * FROM t WHERE [Col_char(30)] > @p, viene ancora scelto varchar(8000) perché causa una conversione del tipo (b) e perché un'altra scelta, ad esempio varchar(4000), causerebbe una conversione del tipo (d).

Come esempio finale, data una query SELECT NULL + @p, viene scelto int per @p perché comporta una conversione del tipo (c).

Autorizzazioni

Richiede l'autorizzazione per eseguire l'istruzione @tsql.

Esempi

Nell'esempio seguente vengono restituite informazioni quali il tipo di dati previsto per i parametri @id e @name non dichiarati.

sp_describe_undeclared_parameters @tsql = 
N'SELECT object_id, name, type_desc 
FROM sys.indexes
WHERE object_id = @id OR name = @name'

Quando il parametro @id viene specificato come un riferimento @params, il parametro @id viene omesso dal set di risultati e solo il parametro @name viene descritto.

sp_describe_undeclared_parameters @tsql = 
N'SELECT object_id, name, type_desc 
FROM sys.indexes
WHERE object_id = @id OR NAME = @name',
@params = N'@id int'

Vedere anche

Riferimento

sp_describe_first_result_set (Transact-SQL)

sys.dm_exec_describe_first_result_set (Transact-SQL)

sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)