sp_describe_undeclared_parameters (Transact-SQL)

Artículo
08/21/2024

Se aplica a: SQL Server Base de datos de Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Punto de conexión de análisis SQL en Microsoft Fabric Almacén en Microsoft Fabric

Devuelve un conjunto de resultados que contiene metadatos sobre los parámetros no declarados de un lote de Transact-SQL. Considera cada parámetro que se utiliza en el lote @tsql, pero no se declara en @params. Se devuelve un conjunto de resultados que contiene una fila para cada parámetro, con la información de tipo deducida para dicho parámetro. El procedimiento devuelve un conjunto de resultados vacío si el lote de entrada de @tsql no tiene ningún parámetro excepto los declarados en @params.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Nota:

Para usar este procedimiento almacenado en Azure Synapse Analytics en un grupo de SQL dedicado, establezca el nivel 20 de compatibilidad de la base de datos en o superior. Para no participar, cambie el nivel de compatibilidad de la base de datos a 10.

Argumentos

[ @tsql = ] 'tsql'

Una o varias instrucciones Transact-SQL. @tsql puede ser nvarchar(n) o nvarchar(max).

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

@params proporciona una cadena de declaración para los parámetros del lote de Transact-SQL, de forma similar al funcionamientosp_executesql. @params puede ser nvarchar(n) o nvarchar(max).

Cadena que contiene las definiciones de todos los parámetros incrustados en @tsql. La cadena debe ser una constante Unicode o una variable Unicode. Cada definición de parámetro se compone de un nombre de parámetro y un tipo de datos. n es un marcador de posición que indica definiciones de parámetros adicionales. Si la instrucción Transact-SQL o el lote de la instrucción no contiene parámetros, no se requiere @params . El valor predeterminado para este parámetro es NULL.

Valores de código de retorno

sp_describe_undeclared_parameters siempre devuelve el estado cero si se realiza correctamente. Si el procedimiento produce un error y se llama al procedimiento como RPC, el estado devuelto se rellena mediante el tipo de error tal como se describe en la error_type columna de sys.dm_exec_describe_first_result_set. Si se llama al procedimiento desde Transact-SQL, el valor devuelto siempre es cero, incluso si se produce un error.

Conjunto de resultados

sp_describe_undeclared_parameters devuelve el siguiente conjunto de resultados.

Nombre de la columna	Tipo de datos	Descripción
`parameter_ordinal`	int	Contiene la posición ordinal del parámetro en el conjunto de resultados. La posición del primer parámetro se especifica como `1`. No acepta valores NULL.
`name`	sysname	Contiene el nombre del parámetro. No acepta valores NULL.
`suggested_system_type_id`	int	Contiene el `system_type_id` del tipo de datos del parámetro tal como se especifica en `sys.types`. En el caso de los tipos CLR, aunque la `system_type_name` columna devuelve `NULL`, esta columna devuelve el valor `240`. No acepta valores NULL.
`suggested_system_type_name`	nvarchar(256)	Contiene el nombre del tipo de datos. Incluye los argumentos (como length, precision y scale) especificados para el tipo de datos del parámetro. Si el tipo de datos es un tipo de alias definido por el usuario, el tipo de sistema subyacente se especifica aquí. Si es un tipo de datos definido por el usuario clR, `NULL` se devuelve en esta columna. Si no se puede deducir el tipo del parámetro, `NULL` se devuelve. Acepta valores NULL.
`suggested_max_length`	smallint	Vea `sys.columns`. para la `max_length` descripción de columna. No acepta valores NULL.
`suggested_precision`	tinyint	Vea `sys.columns`. para obtener la descripción de la columna de precisión. No acepta valores NULL.
`suggested_scale`	tinyint	Vea `sys.columns`. para obtener la descripción de la columna de escala. No acepta valores NULL.
`suggested_user_type_id`	int	Para los tipos clR y alias, contiene el `user_type_id` del tipo de datos de la columna tal como se especifica en `sys.types`. De lo contrario, es `NULL`. Acepta valores NULL.
`suggested_user_type_database`	sysname	Para los tipos de alias y CLR, contiene el nombre de la base de datos en la que se define el tipo. De lo contrario, es `NULL`. Acepta valores NULL.
`suggested_user_type_schema`	sysname	Para los tipos de alias y CLR, contiene el nombre del esquema en el que se define el tipo. De lo contrario, es `NULL`. Acepta valores NULL.
`suggested_user_type_name`	sysname	Para los tipos de alias y CLR, contiene el nombre del tipo. De lo contrario, es `NULL`.
`suggested_assembly_qualified_type_name`	nvarchar(4000)	Para los tipos de CLR, devuelve el nombre del ensamblado y la clase que define el tipo. De lo contrario, es `NULL`. Acepta valores NULL.
`suggested_xml_collection_id`	int	Contiene el `xml_collection_id` del tipo de datos del parámetro tal como se especifica en `sys.columns`. Esta columna devuelve `NULL` si el tipo devuelto no está asociado a una colección de esquemas XML. Acepta valores NULL.
`suggested_xml_collection_database`	sysname	Contiene la base de datos en la que se define la colección de esquema XML asociado a este tipo. Esta columna devuelve `NULL` si el tipo devuelto no está asociado a una colección de esquemas XML. Acepta valores NULL.
`suggested_xml_collection_schema`	sysname	Contiene el esquema en el que se define la colección de esquema XML asociado a este tipo. Esta columna devuelve `NULL` si el tipo devuelto no está asociado a una colección de esquemas XML. Acepta valores NULL.
`suggested_xml_collection_name`	sysname	Contiene el nombre de la colección de esquema XML asociado a este tipo. Esta columna devuelve `NULL` si el tipo devuelto no está asociado a una colección de esquemas XML. Acepta valores NULL.
`suggested_is_xml_document`	bit	Devuelve `1` si el tipo que se devuelve es XML y se garantiza que ese tipo sea un documento XML. De lo contrario, devuelve `0`. No acepta valores NULL.
`suggested_is_case_sensitive`	bit	Devuelve `1` si la columna es de un tipo de cadena que distingue mayúsculas de minúsculas y `0` si no lo es. No acepta valores NULL.
`suggested_is_fixed_length_clr_type`	bit	Devuelve `1` si la columna es de un tipo CLR de longitud fija y `0` si no lo es. No acepta valores NULL.
`suggested_is_input`	bit	Devuelve `1` si el parámetro se usa en cualquier lugar distinto del lado izquierdo de una asignación. De lo contrario, devuelve `0`. No acepta valores NULL.
`suggested_is_output`	bit	Devuelve `1` si el parámetro se usa en el lado izquierdo de una asignación o se pasa a un parámetro de salida de un procedimiento almacenado. De lo contrario, devuelve `0`. No acepta valores NULL.
`formal_parameter_name`	sysname	Si el parámetro es un argumento para un procedimiento almacenado o una función definida por el usuario, devuelve el nombre del parámetro formal correspondiente. De lo contrario, devuelve `NULL`. Acepta valores NULL.
`suggested_tds_type_id`	int	Para uso interno. No acepta valores NULL.
`suggested_tds_length`	int	Para uso interno. No acepta valores NULL.

Comentarios

sp_describe_undeclared_parameters siempre devuelve el estado de cero.

El uso más común es cuando se proporciona a una aplicación una instrucción Transact-SQL que podría contener parámetros y debe procesarlos de alguna manera. Un ejemplo es una interfaz de usuario (como ODBCTest o RowsetViewer) donde el usuario proporciona una consulta con sintaxis de parámetro ODBC. La aplicación debe detectar el número de parámetros dinámicamente y pedir confirmación al usuario para cada uno.

Otro ejemplo es cuando no hay datos proporcionados por el usuario y una aplicación debe recorrer los parámetros y obtener los datos para ellos desde alguna otra ubicación (como una tabla). En este caso, la aplicación no tiene que pasar toda la información de parámetros a la vez. En su lugar, la aplicación puede obtener toda la información de parámetros del proveedor y obtener el propio dato de la tabla. El uso de sp_describe_undeclared_parameters código es más genérico y es menos probable que requiera modificaciones si la estructura de datos cambia más adelante.

sp_describe_undeclared_parameters devuelve un error en cualquiera de los casos siguientes.

El @tsql de entrada no es un lote válido de Transact-SQL. La validez se determina analizando el lote de Transact-SQL. Los errores causados por el lote durante la optimización de consultas o durante la ejecución no se consideran al determinar si el lote de Transact-SQL es válido.
@params no NULL es y contiene una cadena que no es una cadena de declaración sintácticamente válida para los parámetros, o si contiene una cadena que declara cualquier parámetro más de una vez.
El lote transact-SQL de entrada declara una variable local con el mismo nombre que un parámetro declarado en @params.
La instrucción hace referencia a tablas temporales.
La consulta incluye la creación de una tabla permanente que se consulta.

Si @tsql no tiene parámetros, excepto los parámetros declarados en @params, el procedimiento devuelve un conjunto de resultados vacío.

Nota:

Debe declarar la variable como una variable de Transact-SQL escalar o aparece un error.

Algoritmo de selección de parámetros

En una consulta con parámetros no declarados, la deducción de su tipo de datos se realiza en tres pasos.

Paso 1: Buscar los tipos de datos de las subexpresiones

El primer paso en la deducción de tipos de datos para una consulta con parámetros no declarados es buscar los tipos de datos de todas las subexpresiones cuyos tipos de datos no dependen de los parámetros no declarados. El tipo se puede determinar para las siguientes expresiones:

Columnas, constantes, variables y parámetros declarados.
Los resultados de una llamada a una función definida por el usuario (UDF).
Expresión con tipos de datos que no dependen de los parámetros no declarados para todas las entradas.

Por ejemplo, considere la consulta SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. Las expresiones dbo.tbl(@p1) + c1 y c2 tienen tipos de datos, y expresiones @p1 y @p2 + 2 no.

Después de este paso, si alguna expresión (excepto una llamada a un UDF) tiene dos argumentos sin tipos de datos, la deducción del tipo da un error. Por ejemplo, todo lo siguiente produce errores:

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

En el ejemplo siguiente no se produce un error:

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

Paso 2: Buscar expresiones más internas

Para un parámetro @pno declarado determinado, el algoritmo de deducción de tipos busca la expresión E(@p) más interna que contiene @p y es uno de los argumentos siguientes:

Un argumento de un operador de asignación o comparación.
Un argumento de una función definida por el usuario (incluido un UDF con valor de tabla), procedimiento o método.
Argumento para una VALUES cláusula de una INSERT instrucción.
Argumento para o CAST CONVERT.

El algoritmo de deducción de tipos busca un tipo TT(@p) de datos de destino para E(@p). Los tipos de datos de destino de los ejemplos anteriores son los siguientes:

El tipo de datos del otro lado de la comparación o asignación.
El tipo de datos declarado del parámetro al que se pasa este argumento.
El tipo de datos de la columna en la que se inserta este valor.
El tipo de datos al que la instrucción se va a convertir.

Por ejemplo, considere la consulta SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). A continuaciónE(@p1) = @p1, , E(@p2) = @p2 + c1TT(@p1) es el tipo de datos devuelto declarado de y TT(@p2) es el tipo de dbo.tbldatos de parámetro declarado para dbo.tbl.

Si @p no se incluye en ninguna expresión enumerada al principio del paso 2, el algoritmo de deducción de tipos determina que E(@p) es la expresión escalar más grande que contiene @py el algoritmo de deducción de tipos no calcula un tipo TT(@p) de datos de destino para E(@p). Por ejemplo, si la consulta es SELECT @p + 2 , E(@p) = @p + 2y no hay .TT(@p)

Paso 3: Deducir tipos de datos

Ahora que E(@p) y TT(@p) se identifican, el algoritmo de deducción de tipos deduce un tipo de datos de @p una de las dos maneras siguientes:

Deducción simple

Si E(@p) = @p y TT(@p) existe, es decir, si @p es directamente un argumento para una de las expresiones enumeradas al principio del paso 2, el algoritmo de deducción de tipo deduce el tipo de datos de @p para ser TT(@p). Por ejemplo:
```
SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);
```
El tipo de datos para @p1, @p2y @p3 será el tipo de datos de , el tipo de c1datos devuelto de y el tipo de dbo.tbldatos de parámetro para dbo.tbl respectivamente.

Como caso especial, si @p es un argumento para un <operador , >, <=o >= , no se aplican reglas de deducción simples. El algoritmo de deducción de tipo utilizará las reglas de deducción generales explicadas en la sección siguiente. Por ejemplo, si c1 es una columna de tipo de datos char(30), tenga en cuenta las dos consultas siguientes:
```
SELECT * FROM t WHERE c1 = @p;
SELECT * FROM t WHERE c1 > @p;
```
En el primer caso, el algoritmo de deducción de tipos deduce char(30) como el tipo de datos para @p según las reglas anteriores en este artículo. En el segundo caso, el algoritmo de deducción de tipo deduce varchar(8000) según las reglas de deducción generales de la sección siguiente.
Deducción general

Si no se aplica la deducción simple, se tienen en cuenta los siguientes tipos de datos para los parámetros no declarados:
- Tipos de datos enteros (bit, tinyint, smallint, int, bigint)
- Tipos de datos monetarios (smallmoney, money)
- Tipos de datos de punto flotante (float, real)
- numeric(38, 19): no se tienen en cuenta otros tipos de datos numéricos o decimales.
- varchar(8000), varchar(max), nvarchar(4000) y nvarchar(max): otros tipos de datos de cadena (como text, char(8000), nvarchar(30), etc.) no se consideran.
- varbinary(8000) y varbinary(max): no se consideran otros tipos de datos binarios (como image, binary(8000), varbinary(30), etcetera.).
- date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7): no se tienen en cuenta otros tipos de fecha y hora, como time(4).
- sql_variant
- xml
- Tipos definidos por el sistema CLR (hierarchyid, geometry, geography)
- Tipos definidos por el usuario CLR

Criterios de selección

De los tipos de datos candidatos, se rechaza cualesquiera que invalidara la consulta. De los tipos de datos candidatos restantes, el algoritmo de deducción de tipo selecciona uno según las siguientes reglas.

Se selecciona el tipo de datos que genera el menor número de conversiones implícitas de E(@p) . Si un tipo de datos determinado genera un tipo de datos para E(@p) que es diferente de TT(@p), el algoritmo de deducción de tipos considera que se trata de una conversión implícita adicional del tipo de datos de E(@p) a TT(@p).

Por ejemplo:
```
SELECT * FROM t WHERE Col_Int = Col_Int + @p;
```
En este caso, E(@p) es Col_Int + @p y TT(@p) es int. int se elige para @p porque no produce conversiones implícitas. Cualquier otra opción de tipo de datos genera al menos una conversión implícita.
Si hay varios tipos de datos que coinciden en el número menor de conversiones, se utiliza el tipo de datos con mayor prioridad. Por ejemplo:
```
SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
```
En este caso, int y smallint produce una conversión. Otros tipos de datos generan más de una conversión. Dado que int tiene prioridad sobre smallint, se usa int para .@p Para obtener más información sobre la precedencia del tipo de datos, consulte Precedencia de tipos de datos.

Esta regla solo se aplica si hay una conversión implícita entre cada tipo de datos que se vincula según la regla 1 y el tipo de datos con la mayor prioridad. Si no hay ninguna conversión implícita, se produce un error en la deducción del tipo de datos. Por ejemplo, en la consulta SELECT @p FROM t, se produce un error en la deducción del tipo de datos porque cualquier tipo de datos para @p sería igualmente bueno. Por ejemplo, no hay ninguna conversión implícita de int a xml.
Si dos tipos de datos similares se vinculan en la regla 1, por ejemplo varchar(8000) y varchar(max), se elige el tipo de datos más pequeño (varchar(8000)). El mismo principio se aplica a los tipos de datos nvarchar y varbinary.
Para los fines de la regla 1, el algoritmo de deducción de tipo prefiere ciertas conversiones sobre otras. Las conversiones, en orden de mejor a peor, son:
1. Conversión entre el mismo tipo de datos básico de longitud diferente.
2. Conversión entre la versión de longitud fija y de longitud variable de los mismos tipos de datos (por ejemplo, char a varchar).
3. Conversión entre NULL e int.
4. Cualquier otra conversión.

Por ejemplo, para la consulta SELECT * FROM t WHERE [Col_varchar(30)] > @p, se elige varchar(8000) porque la conversión (a) es mejor. Para la consulta SELECT * FROM t WHERE [Col_char(30)] > @p, se sigue eligiendo varchar(8000) porque produce una conversión de tipo (b) y porque otra opción (como varchar(4000)) produciría una conversión de tipo (d).

Como ejemplo final, dada una consulta SELECT NULL + @p, se elige int porque @p da como resultado una conversión de tipo (c).

Permisos

Requiere permiso para ejecutar el argumento @tsql .

Ejemplos

En el siguiente ejemplo se devuelve información como el tipo de datos esperado para los parámetros @id e @name no declarados.

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

Cuando se proporciona el parámetro @id como una referencia @params, el parámetro @id se omite del conjunto de resultados y solo se describe el parámetro @name.

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

Compartir a través de

sp_describe_undeclared_parameters (Transact-SQL)

Sintaxis

Argumentos

[ @tsql = ] 'tsql'

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

Valores de código de retorno

Conjunto de resultados

Comentarios

Algoritmo de selección de parámetros

Paso 1: Buscar los tipos de datos de las subexpresiones

Paso 2: Buscar expresiones más internas

Paso 3: Deducir tipos de datos

Criterios de selección

Permisos

Ejemplos

Comentarios

Recursos adicionales

Compartir a través de

sp_describe_undeclared_parameters (Transact-SQL)

Sintaxis

Argumentos

[ @tsql = ] 'tsql'

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

Valores de código de retorno

Conjunto de resultados

Comentarios

Algoritmo de selección de parámetros

Paso 1: Buscar los tipos de datos de las subexpresiones

Paso 2: Buscar expresiones más internas

Paso 3: Deducir tipos de datos

Criterios de selección

Permisos

Ejemplos

Contenido relacionado

Comentarios

Recursos adicionales