Condividi tramite

sp_describe_undeclared_parameters (Transact-SQL)

  • Articolo

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 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'params [ , ...n ]' ]

Nota

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

Argomenti

[ @tsql = ] 'tsql'

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

[ @params = ] N'params'

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

Nome colonna Tipo di dati Descrizione
parameter_ordinal int Contiene la posizione ordinale del parametro nel set di risultati. La posizione del primo parametro viene specificata come 1. Non ammette i valori NULL.
name sysname Contiene il nome del parametro. Non ammette i valori NULL.
suggested_system_type_id int Contiene l'oggetto system_type_id del tipo di dati del parametro come specificato in sys.types.

Per i tipi CLR, anche se la system_type_name colonna restituisce NULL, questa colonna restituisce il valore 240. Non ammette i valori NULL.
suggested_system_type_name nvarchar(256) 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 si tratta di un tipo di dati CLR definito dall'utente, NULL viene restituito in questa colonna. Se non è possibile dedurre il tipo del parametro, NULL viene restituito . Ammette valori Null.
suggested_max_length smallint Vedere sys.columns. per max_length la descrizione della colonna. Non ammette i valori NULL.
suggested_precision tinyint Vedere sys.columns. per la descrizione della colonna PRECISION. Non ammette i valori NULL.
suggested_scale tinyint Vedere sys.columns. per la descrizione della colonna SCALE. Non ammette i valori NULL.
suggested_user_type_id int Per i tipi CLR e alias, contiene l'oggetto user_type_id del tipo di dati della colonna come specificato in sys.types. In caso contrario, è NULL. Ammette valori Null.
suggested_user_type_database sysname Per i tipi di alias e CLR, contiene il nome del database in cui è definito il tipo. In caso contrario, è NULL. Ammette valori Null.
suggested_user_type_schema sysname Per i tipi di alias e CLR, contiene il nome dello schema in cui è definito il tipo. In caso contrario, è NULL. Ammette valori Null.
suggested_user_type_name sysname Per i tipi di alias e CLR, contiene il nome del tipo. In caso contrario, è NULL.
suggested_assembly_qualified_type_name nvarchar(4000) Per i tipi CLR, restituisce il nome dell'assembly e la classe che definisce il tipo. In caso contrario, è NULL. Ammette valori Null.
suggested_xml_collection_id int Contiene l'oggetto 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 di XML Schema. Ammette valori Null.
suggested_xml_collection_database sysname 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 di XML Schema. Ammette valori Null.
suggested_xml_collection_schema sysname 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 di XML Schema. Ammette valori Null.
suggested_xml_collection_name sysname Contiene il nome della raccolta XML Schema associata a questo tipo. Questa colonna restituisce NULL se il tipo restituito non è associato a una raccolta di XML Schema. Ammette valori Null.
suggested_is_xml_document bit Restituisce 1 se il tipo restituito è XML e tale tipo è garantito che sia un documento XML. In caso contrario restituisce 0. Non ammette i valori NULL.
suggested_is_case_sensitive bit Restituisce 1 se la colonna è di un tipo stringa con distinzione tra maiuscole e minuscole e 0 in caso contrario. Non ammette i valori NULL.
suggested_is_fixed_length_clr_type bit Restituisce 1 se la colonna è di un tipo CLR a lunghezza fissa e 0 in caso contrario. Non ammette i valori NULL.
suggested_is_input bit Restituisce 1 se il parametro viene utilizzato in qualsiasi punto diverso dal lato sinistro di un'assegnazione. In caso contrario restituisce 0. Non ammette i valori NULL.
suggested_is_output bit Restituisce 1 se il parametro viene utilizzato sul lato sinistro di un'assegnazione o viene passato a un parametro di output di una stored procedure. In caso contrario restituisce 0. Non ammette i valori NULL.
formal_parameter_name sysname 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. Ammette valori Null.
suggested_tds_type_id int Per uso interno. Non ammette i valori NULL.
suggested_tds_length int Per uso interno. Non ammette i valori NULL.

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.

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: 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