sp_describe_undeclared_parameters (Transact-SQL)

Articolo
08/21/2023

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Endpoint di analisi SQL di Azure Synapse Analytics in Microsoft FabricWarehouse in Microsoft Fabric

Restituisce un set di risultati che contiene metadati relativi ai parametri non dichiarati in un batch Transact-SQL. Considera ogni parametro usato 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 routine restituisce un set di risultati vuoto se il batch di input @tsql non ha parametri tranne quelli dichiarati in @params.

Convenzioni di sintassi Transact-SQL

Sintassi

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

Nota

Per usare questa stored procedure in Azure Synapse Analytics nel pool SQL dedicato, impostare il livello di compatibilità del database su 20 o versione successiva. Per rifiutare esplicitamente, impostare il livello di compatibilità del database su 10.

Argomenti

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

[ @params = ] N'parameters'' @params fornisce una stringa di dichiarazione per i parametri per il batch Transact-SQL, in modo analogo al funzionamento sp_executesql. I parametri possono 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 del codice restituito

sp_describe_undeclared_parameters restituisce sempre lo stato restituito zero in caso di esito positivo. Se la routine genera un errore e la routine viene chiamata rpc, 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 nei casi 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 la 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 restituirà NULL, questa colonna restituirà 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 max_length descrizione della colonna.
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 la 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, viene restituito 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 lo stato restituito zero.

L'uso più comune è quando a un'applicazione viene assegnata un'istruzione Transact-SQL che potrebbe contenere parametri e deve elaborarli in qualche modo. Un esempio è un'interfaccia utente, ad esempio ODBCTest o RowsetViewer, in cui l'utente fornisce una query con la sintassi dei parametri 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 usa sp_describe_undeclared_parameters è più generico ed è meno probabile che richieda modifiche se la struttura dei dati cambia in un secondo momento.

sp_describe_undeclared_parameters restituisce un errore in uno dei casi seguenti.

Se il @tsql di input non è un batch Transact-SQL valido. La validità è determinata dall'analisi e dall'analisi del batch Transact-SQL. Eventuali errori causati dal batch durante l'ottimizzazione delle query o durante l'esecuzione non vengono considerati quando si determina se il batch Transact-SQL è valido.
Se @params non è NULL e contiene una stringa che non è una stringa di dichiarazione sintatticamente valida per i parametri o se contiene una stringa che dichiara un parametro più di una volta.
Se il batch Transact-SQL di input dichiara una variabile locale con lo stesso nome di un parametro dichiarato in @params.
Se l'istruzione fa riferimento a tabelle temporanee.
La query include la creazione di una tabella permanente sulla quale viene eseguita una query.

Se @tsql non dispone di parametri diversi da quelli dichiarati in @params, la routine restituisce un set di risultati vuoto.

Nota

È necessario dichiarare la variabile come variabile Transact-SQL scalare oppure viene visualizzato un errore.

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. Le espressioni dbo.tbl(@p1) + c1 e c2 hanno tipi di dati e l'espressione @p1 e @p2 + 2 non lo fanno.

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 del tipo trova l'espressione più interna E(@p) che contiene @p ed è una delle 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 di una clausola VALUES di un'istruzione IN edizione Standard RT.
Argomento di cast o CONVERT.

L'algoritmo di deduzione del tipo 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 del tipo determina che E(@p) è l'espressione scalare più grande che contiene @p e l'algoritmo di deduzione del tipo non calcola un tipo di dati di destinazione TT(@p) per E(@p). Ad esempio, se la query è edizione Standard LECT@p + 2, E(@p) = @p + 2 e non è presente TT(@p).

Passaggio 3

Ora che vengono identificati E(@p) e TT(@p), l'algoritmo di deduzione del tipo deduce un tipo di dati per @p in uno dei due modi seguenti:

Deduzione semplice

Se E(@p) = @p e TT(@p) esiste, ad esempio se @p è direttamente un argomento a una delle espressioni elencate all'inizio del passaggio 2, l'algoritmo di deduzione del tipo deduce il tipo di dati di @p essere 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 c1, il tipo di dati restituito dbo.tbl e il tipo di dati del parametro per dbo.tbl.

Come caso speciale, se @p è un argomento per un <operatore , , = ><o >= , le regole di deduzione semplici 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 del tipo deduce char(30) come tipo di dati per @p in base alle regole riportate in precedenza in questo argomento. Nel secondo caso, l'algoritmo di deduzione del tipo deduce varchar(8000) in base alle regole generali di deduzione 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): altri tipi di dati numerici o decimali non vengono considerati.
- varchar(8000), varchar(max), nvarchar(4000)e nvarchar(max) - Altri tipi di dati stringa (ad esempio text, char(8000), nvarchar(30)e così via) non sono considerati.
- varbinary(8000) e varbinary(max): altri tipi di dati binari non vengono considerati (ad esempio image, binary(8000), varbinary(30)e così via.
- date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - Altri tipi di data e ora, ad esempio time(4), non vengono considerati.
- 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.

Viene selezionato il tipo di dati che produce il numero più piccolo di conversioni implicite in E(@p). Se un tipo di dati specifico produce un tipo di dati per E(@p) diverso da TT(@p), l'algoritmo di deduzione del tipo considera che si tratta di una conversione implicita aggiuntiva dal tipo di dati 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.
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 usato per @p. Per altre informazioni sulla precedenza del tipo di dati, vedere Precedenza del tipo 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 tla deduzione del tipo di dati ha esito negativo perché qualsiasi tipo di dati per @p sarebbe altrettanto valido. Ad esempio, non esiste alcuna conversione implicita da int a xml.
Se due tipi di dati simili sono legati alla regola 1, ad esempio varchar(8000) e varchar(max), viene scelto il tipo di dati più piccolo (varchar(8000)). Lo stesso principio si applica ai tipi di dati nvarchar e varbinary .
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 a lunghezza variabile degli stessi tipi di dati , ad esempio char in varchar.
3. Conversione tra NULL e int.
4. Qualsiasi altra conversione.

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

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

Autorizzazioni

È necessaria l'autorizzazione per eseguire l'argomento @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'