sp_describe_undeclared_parameters (Transact-SQL)

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Endpoint di analisi SQL in Microsoft Fabric Warehouse 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 relative alla sintassi Transact-SQL

Sintassi

sp_describe_undeclared_parameters
    [ @tsql = ] 'Transact-SQL_batch'
    [ , [ @params = ] N'@parameter_name data_type [ , ... n ]' ]

Argomenti

[ @tsql = ] 'tsql'

Una o più istruzioni Transact-SQL. @tsql potrebbe essere nvarchar(n) o nvarchar(max).

[ @params = ] N'@parameter_name data_type [ ,... n ]'

@params fornisce una stringa di dichiarazione per i parametri per il batch Transact-SQL, in modo analogo al funzionamentosp_executesql. @params potrebbe essere nvarchar(n) o nvarchar(max).

Stringa che contiene le definizioni di tutti i parametri incorporati in @tsql. 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.

Valori del codice restituito

sp_describe_undeclared_parameters restituisce sempre lo stato zero in caso di esito positivo. Se la routine genera un errore e la routine viene chiamata come RPC, lo stato restituito viene popolato dal tipo di errore come descritto nella error_type colonna 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.

Osservazioni:

sp_describe_undeclared_parameters restituisce sempre lo stato 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, l'applicazione non deve passare tutte le informazioni sui parametri contemporaneamente. ma è possibile ottenerle dal provider e acquisire i dati dalla tabella. L'uso del sp_describe_undeclared_parameters codice è più generico ed è meno probabile che sia necessario modificare se la struttura dei dati viene modificata in un secondo momento.

sp_describe_undeclared_parameters restituisce un errore in uno dei casi seguenti.

• 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.

• @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.

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

• 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 dai parametri dichiarati in @params, la routine 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: Trovare i tipi di dati delle espressioni secondarie

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 sottoespressione 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 dispongono di tipi di dati ed espressione @p1 e @p2 + 2 non.

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);

L'esempio seguente non genera un errore:

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

Passaggio 2: Trovare espressioni più interne

Per un determinato parametro @pnon dichiarato , l'algoritmo di deduzione del tipo trova l'espressione E(@p) più interna che contiene @p ed è uno degli argomenti 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 VALUES clausola di un'istruzione INSERT .
• Argomento di un CAST oggetto o CONVERT.

L'algoritmo di deduzione del tipo trova un tipo di dati di TT(@p) destinazione 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). Quindi E(@p1) = @p1, E(@p2) = @p2 + c1è TT(@p1) il tipo di dati restituito dichiarato di e TT(@p2) è il tipo di dbo.tbldati 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 @pe l'algoritmo di deduzione del tipo non calcola un tipo di dati di TT(@p) destinazione per E(@p). Ad esempio, se la query è SELECT @p + 2 , E(@p) = @p + 2e non TT(@p)è presente .

Passaggio 3: Dedurre i tipi di dati

Ora che E(@p) e TT(@p) sono identificati, 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, ovvero 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 come TT(@p). Ad esempio:

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

  Il tipo di dati per @p1, @p2e @p3 sarà il tipo di dati , c1il tipo di dati restituito di dbo.tble il tipo di dati del parametro rispettivamente.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. Ad esempio, se c1 è una colonna di tipo di dati char(30),prendere in considerazione 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 come indicato nelle regole precedenti in questo articolo. 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 si applica, i tipi di dati seguenti vengono considerati 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.

1. Il tipo di dati che produce il numero minimo di conversioni implicite in E(@p) è selezionato. Se un tipo di dati specifico produce un tipo di dati diverso E(@p) da TT(@p), l'algoritmo di deduzione del tipo considera che si tratta di 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 usato per @p. Per altre informazioni sulla precedenza del tipo di dati, vedere Precedenza del tipo di dati.

   Questa regola si applica solo se è presente una conversione implicita tra ogni tipo di dati che lega in base alla regola 1 e al tipo di dati con la precedenza maggiore. Se non è presente alcuna conversione implicita, la deduzione del tipo di dati non riesce con un errore. Ad esempio, nella query SELECT @p FROM t, la 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.

3. 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 .

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 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 perché @p 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.

EXEC 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.

EXEC 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';

Contenuto correlato

• 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)