sp_describe_undeclared_parameters(Transact-SQL)

적용 대상: Microsoft Fabric의 Microsoft Fabric Warehouse에 있는 SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL 분석 엔드포인트

Transact-SQL 일괄 처리에서 선언되지 않은 매개 변수에 대한 메타데이터를 포함하는 결과 집합을 반환합니다. @tsql 일괄 처리에서 사용되지만 @params 선언되지 않은 각 매개 변수를 고려합니다. 해당 매개 변수에 대한 추론된 형식 정보와 함께 이러한 각 매개 변수에 대해 하나의 행을 포함하는 결과 집합이 반환됩니다. 프로시저는 @tsql 입력 일괄 처리에 @params 선언된 매개 변수를 제외한 매개 변수가 없는 경우 빈 결과 집합을 반환합니다.

Transact-SQL 구문 표기 규칙

Syntax

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

참고 항목

전용 SQL 풀의 Azure Synapse Analytics에서 이 저장 프로시저를 사용하려면 데이터베이스 호환성 수준을 20 이상으로 설정합니다. 옵트아웃하려면 데이터베이스 호환성 수준을 .로 변경합니다 10.

인수

[ @tsql = ] 'tsql'

하나 이상의 Transact-SQL 문입니다. @tsql nvarchar(n) 또는 nvarchar(max)일 수 있습니다.

[ @params = ] N'params'

@params 작동 방식 sp_executesql 과 유사하게 Transact-SQL 일괄 처리에 대한 매개 변수에 대한 선언 문자열을 제공합니다. @params nvarchar(n) 또는 nvarchar(max)일 수 있습니다.

@tsql 포함된 모든 매개 변수의 정의를 포함하는 문자열입니다. 문자열은 유니코드 상수 또는 유니코드 변수여야 합니다. 각 매개 변수 정의는 매개 변수 이름과 데이터 형식으로 구성됩니다. n 은 추가 매개 변수 정의를 나타내는 자리 표시자입니다. 문의 Transact-SQL 문 또는 일괄 처리에 매개 변수 가 포함되어 있지 않으면 @params 필요하지 않습니다. 이 매개 변수의 기본값은 NULL입니다.

반환 코드 값

sp_describe_undeclared_parameters 는 성공하면 항상 0의 상태를 반환합니다. 프로시저에서 오류를 throw하고 프로시저가 RPC로 호출되면 반환 상태는 열sys.dm_exec_describe_first_result_set에 error_type 설명된 대로 오류 유형으로 채워집니다. Transact-SQL에서 프로시저를 호출하는 경우 오류 발생 시에도 반환 값은 항상 0입니다.

결과 집합

sp_describe_undeclared_parameters은 다음 결과 집합을 반환합니다.

열 이름	데이터 형식	설명
parameter_ordinal	int	결과 집합에 있는 매개 변수의 서수 위치를 포함합니다. 첫 번째 매개 변수의 위치는 .로 1지정됩니다. Null을 허용하지 않습니다.
name	sysname	매개 변수의 이름을 포함합니다. Null을 허용하지 않습니다.
suggested_system_type_id	int	에 system_type_id 지정된 sys.types대로 매개 변수의 데이터 형식을 포함합니다. CLR 형식의 경우 열이 system_type_name 반환 NULL되더라도 이 열은 값을 240반환합니다. Null을 허용하지 않습니다.
suggested_system_type_name	nvarchar(256)	데이터 형식 이름을 포함합니다. 매개 변수의 데이터 형식에 지정된 인수(예: 길이, 정밀도, 소수 자릿수)를 포함합니다. 데이터 형식이 사용자 정의 별칭 형식인 경우 기본 시스템 형식이 여기에 지정됩니다. CLR 사용자 정의 데이터 형식 NULL 인 경우 이 열에 반환됩니다. 매개 변수의 형식을 추론 NULL 할 수 없는 경우 반환됩니다. Nullable.
suggested_max_length	smallint	sys.columns을 참조하세요. 열 설명의 경우 max_length Null을 허용하지 않습니다.
suggested_precision	tinyint	sys.columns을 참조하세요. 참조하세요. Null을 허용하지 않습니다.
suggested_scale	tinyint	sys.columns을 참조하세요. 크기 조정 열 설명에 대해 설명합니다. Null을 허용하지 않습니다.
suggested_user_type_id	int	CLR 및 별칭 형식의 경우 에 지정된 sys.types대로 열의 데이터 형식을 포함합니다user_type_id. 그렇지 않으면 .입니다 NULL. Nullable.
suggested_user_type_database	sysname	CLR 및 별칭 형식의 경우 해당 형식이 정의된 데이터베이스의 이름을 포함합니다. 그렇지 않으면 .입니다 NULL. Nullable.
suggested_user_type_schema	sysname	CLR 및 별칭 형식의 경우 해당 형식이 정의된 스키마의 이름을 포함합니다. 그렇지 않으면 .입니다 NULL. Nullable.
suggested_user_type_name	sysname	CLR 및 별칭 형식의 경우 형식 이름입니다. 그렇지 않으면 .입니다 NULL.
suggested_assembly_qualified_type_name	nvarchar(4000)	CLR 형식의 경우 형식을 정의하는 어셈블리 및 클래스의 이름을 반환합니다. 그렇지 않으면 .입니다 NULL. Nullable.
suggested_xml_collection_id	int	에 xml_collection_id 지정된 sys.columns대로 매개 변수의 데이터 형식을 포함합니다. 반환된 형식이 XML 스키마 컬렉션과 연결되지 않은 경우 이 열이 반환 NULL 됩니다. Nullable.
suggested_xml_collection_database	sysname	이 형식과 연결된 XML 스키마 컬렉션이 정의된 데이터베이스를 포함합니다. 반환된 형식이 XML 스키마 컬렉션과 연결되지 않은 경우 이 열이 반환 NULL 됩니다. Nullable.
suggested_xml_collection_schema	sysname	이 형식과 연결된 XML 스키마 컬렉션이 정의된 스키마를 포함합니다. 반환된 형식이 XML 스키마 컬렉션과 연결되지 않은 경우 이 열이 반환 NULL 됩니다. Nullable.
suggested_xml_collection_name	sysname	이 형식과 연결된 XML 스키마 컬렉션의 이름을 포함합니다. 반환된 형식이 XML 스키마 컬렉션과 연결되지 않은 경우 이 열이 반환 NULL 됩니다. Nullable.
suggested_is_xml_document	bit	반환되는 형식이 XML이고 해당 형식이 XML 문서로 보장되는지를 반환 1 합니다. 그렇지 않으면 0를 반환합니다. Null을 허용하지 않습니다.
suggested_is_case_sensitive	bit	1 열이 대/소문자를 구분하는 문자열 형식이고 0 그렇지 않은 경우 반환합니다. Null을 허용하지 않습니다.
suggested_is_fixed_length_clr_type	bit	1 열이 고정 길이 CLR 형식 0 이고 그렇지 않은 경우 반환합니다. Null을 허용하지 않습니다.
suggested_is_input	bit	1 매개 변수가 할당의 왼쪽이 아닌 다른 곳에서 사용되는지 반환합니다. 그렇지 않으면 0를 반환합니다. Null을 허용하지 않습니다.
suggested_is_output	bit	매개 변수가 할당의 왼쪽에서 사용되거나 저장 프로시저의 출력 매개 변수에 전달되는지 여부를 반환 1 합니다. 그렇지 않으면 0를 반환합니다. Null을 허용하지 않습니다.
formal_parameter_name	sysname	매개 변수가 저장 프로시저 또는 사용자 정의 함수에 대한 인수인 경우 해당 정식 매개 변수의 이름을 반환합니다. 그렇지 않으면 NULL를 반환합니다. Nullable.
suggested_tds_type_id	int	내부용. Null을 허용하지 않습니다.
suggested_tds_length	int	내부용. Null을 허용하지 않습니다.

설명

sp_describe_undeclared_parameters 는 항상 0의 상태를 반환합니다.

가장 일반적인 용도는 애플리케이션에 매개 변수를 포함할 수 있고 어떤 방식으로든 처리해야 하는 Transact-SQL 문이 제공되는 경우입니다. 예를 들어 사용자가 ODBC 매개 변수 구문을 사용하여 쿼리를 제공하는 사용자 인터페이스(예: ODBCTest 또는 RowsetViewer)가 있습니다. 애플리케이션은 매개 변수 수를 동적으로 검색하고 각 매개 변수에 대해 사용자에게 메시지를 표시해야 합니다.

또 다른 예는 사용자 입력이 없으면 애플리케이션이 매개 변수를 반복하고 다른 위치(예: 테이블)에서 해당 매개 변수에 대한 데이터를 가져와야 하는 경우입니다. 이 경우 애플리케이션은 모든 매개 변수 정보를 한 번에 전달할 필요가 없습니다. 대신 애플리케이션은 공급자로부터 모든 매개 변수 정보를 가져오고 테이블에서 데이터 자체를 가져올 수 있습니다. 코드 사용 sp_describe_undeclared_parameters 은 더 일반적이며 나중에 데이터 구조가 변경되면 수정이 필요하지 않습니다.

sp_describe_undeclared_parameters 는 다음 경우 중에서 오류를 반환합니다.

• 입력 @tsql 유효한 Transact-SQL 일괄 처리가 아닙니다. 유효성은 Transact-SQL 일괄 처리를 구문 분석하고 분석하여 결정됩니다. 쿼리 최적화 중 또는 실행 중에 일괄 처리로 인한 오류는 Transact-SQL 일괄 처리가 유효한지 여부를 결정할 때 고려되지 않습니다.

• NULL@params 매개 변수에 대해 구문적으로 유효한 선언 문자열이 아니거나 매개 변수를 두 번 이상 선언하는 문자열이 포함된 경우 문자열을 포함합니다.

• 입력 Transact-SQL 일괄 처리는 @params 선언된 매개 변수와 동일한 이름의 지역 변수를 선언합니다.

• 이 문은 임시 테이블을 참조합니다.

• 쿼리에 다음으로 쿼리되는 영구 테이블 생성이 포함되는 경우

@tsql @params 선언된 매개 변수 이외의 매개 변수가 없는 경우 프로시저는 빈 결과 집합을 반환합니다.

참고 항목

변수를 스칼라 Transact-SQL 변수로 선언해야 합니다. 그렇지 않으면 오류가 나타납니다.

매개 변수 선택 알고리즘

선언되지 않은 매개 변수가 있는 쿼리의 경우 선언되지 않은 매개 변수에 대한 데이터 형식 추론은 세 단계로 진행됩니다.

1단계: 하위 식의 데이터 형식 찾기

선언되지 않은 매개 변수가 있는 쿼리에 대한 데이터 형식 추론의 첫 번째 단계는 데이터 형식이 선언되지 않은 매개 변수에 종속되지 않는 모든 하위 식의 데이터 형식을 찾는 것입니다. 다음 식에 대해 형식을 확인할 수 있습니다.

• 열, 상수, 변수 및 선언된 매개 변수
• UDF(사용자 정의 함수)에 대한 호출 결과입니다.
• 모든 입력에 대해 선언되지 않은 매개 변수에 의존하지 않는 데이터 형식이 있는 식입니다.

예를 들어 SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2 쿼리를 고려할 수 있습니다. 식과 dbo.tbl(@p1) + c1 c2 데이터 형식 및 식 @p1 이 있으며 @p2 + 2 그렇지 않습니다.

이 단계 후에 UDF 호출 이외의 식에 데이터 형식이 없는 두 개의 인수가 있는 경우 오류와 함께 형식 추론이 실패합니다. 예를 들어 다음과 같은 오류는 모두 발생합니다.

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

다음 예제에서는 오류를 생성하지 않습니다.

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

2단계: 가장 안쪽에 있는 식 찾기

선언되지 않은 지정된 매개 변수@p의 경우 형식 추론 알고리즘은 다음 인수를 포함하고 @p 있는 가장 안쪽 식을 E(@p) 찾습니다.

• 비교 또는 대입 연산자에 대한 인수입니다.
• 사용자 정의 함수(테이블 반환 UDF 포함), 프로시저 또는 메서드에 대한 인수입니다.
• 문의 절에 VALUES 대한 인수입니다 INSERT .
• 또는 CONVERT에 대한 인수입니다CAST.

형식 추론 알고리즘은 에 대한 대상 데이터 형식 TT(@p) 을 찾습니다 E(@p). 이전 예제의 대상 데이터 형식은 다음과 같습니다.

• 비교 또는 할당의 다른 쪽 데이터 형식입니다.
• 이 인수가 전달되는 매개 변수의 선언된 데이터 형식입니다.
• 이 값이 삽입되는 열의 데이터 형식입니다.
• 문이 캐스팅 또는 변환되는 데이터 형식입니다.

예를 들어 SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1) 쿼리를 고려할 수 있습니다. 그런 다음 E(@p1) = @p1, TT(@p1) E(@p2) = @p2 + c1의 선언된 반환 데이터 형식dbo.tbl이며 TT(@p2) , 에 대해 dbo.tbl선언된 매개 변수 데이터 형식입니다.

2단계의 시작 부분에 나열된 식에 포함되지 않은 경우 @p 형식 추론 알고리즘은 포함하는 E(@p) 가장 큰 스칼라 식임을 @p확인하고 형식 추론 알고리즘은 대상 데이터 형식 TT(@p) 을 E(@p)계산하지 않습니다. 예를 들어 쿼리가 다음E(@p) = @p + 2이고 없는 TT(@p)경우입니다SELECT @p + 2.

3단계: 데이터 형식 추론

이제 식별 E(@p) TT(@p) 된 형식 추론 알고리즘은 다음 두 가지 방법 중 하나로 데이터 형식을 @p 추론합니다.

• 단순 공제

  존재할 TT(@p) 경우 E(@p) = @p 즉, 2단계의 시작 부분에 나열된 식 중 하나에 대한 인수인 경우 @p 형식 추론 알고리즘은 데이터 형식 @p 을 TT(@p)추론합니다. 예시:

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

  에 @p2대한 데이터 형식이며@p3, 각각에 대한 @p1dbo.tbl 데이터 형식c1, 반환 데이터 형식 dbo.tbl및 매개 변수 데이터 형식이 됩니다.

  특별한 경우, , <=>또는 >= 연산자에 대한 인수<인 경우 @p 간단한 공제 규칙이 적용되지 않습니다. 형식 추론 알고리즘은 다음 섹션에 설명된 일반 공제 규칙을 사용합니다. 예를 들어 데이터 형식 char(30)의 열인 경우 c1 다음 두 쿼리를 고려합니다.

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

  첫 번째 경우 형식 추론 알고리즘은 이 문서의 앞부분에 있는 규칙에 따라 char(30)를 @p 데이터 형식으로 추론합니다. 두 번째 경우 형식 추론 알고리즘은 다음 섹션의 일반 공제 규칙에 따라 varchar(8000)를 추론합니다.

• 일반 추론

  단순 추론이 적용되지 않으면 선언되지 않은 매개 변수에 대해 다음 데이터 형식이 고려됩니다.

  • 정수 데이터 형식(bit, tinyint, smallint, int, bigint)

  • Money 데이터 형식(smallmoney, money)

  • 부동 소수점 데이터 형식(float, real)

  • numeric(38, 19) - 다른 숫자 또는 소수 데이터 형식은 고려되지 않습니다.

  • varchar(8000), varchar(max), nvarchar(4000) 및 nvarchar(max) - 기타 문자열 데이터 형식(예: text, char(8000), nvarchar(30) 등) 는 고려되지 않습니다.

  • varbinary(8000) 및 varbinary(max) - 다른 이진 데이터 형식은 고려되지 않습니다(예: image, binary(8000), varbinary(30) 등).

  • date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - time(4)과 같은 다른 날짜 및 시간 형식은 고려되지 않습니다.

  • sql_variant

  • xml

  • CLR 시스템 정의 형식(hierarchyid, geometry, geography)

  • CLR 사용자 정의 형식

선택 조건

후보 데이터 형식 중 쿼리를 무효화하는 데이터 형식은 거부됩니다. 형식 추론 알고리즘은 남은 후보 데이터 형식 중에서 다음 규칙에 따라 하나를 선택합니다.

1. 가장 적은 수의 암시적 변환 E(@p) 을 생성하는 데이터 형식이 선택됩니다. 특정 데이터 형식이 다른 데이터 형식을 E(@p) 생성하는 경우 형식 추론 알고리즘은 TT(@p)이를 데이터 형식에서 로의 E(@p) 추가 암시적 변환으로 TT(@p)간주합니다.

   예시:

   SELECT * FROM t WHERE Col_Int = Col_Int + @p;
   

   이 경우 int E(@p) Col_Int + @p입니다.TT(@p) 암시적 변환을 생성하지 않으므로 int가 선택 @p 됩니다. 다른 데이터 형식 선택은 하나 이상의 암시적 변환을 생성합니다.

2. 여러 데이터 형식이 가장 적은 수의 변환에 연결된 경우 우선 순위가 더 큰 데이터 형식이 사용됩니다. 예시:

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
   

   이 경우 int 및 smallint 는 하나의 변환을 생성합니다. 다른 모든 데이터 형식은 둘 이상의 변환을 생성합니다. int가 smallint보다 우선하기 때문에 int가 사용됩니다@p. 데이터 형식 우선 순위에 대한 자세한 내용은 데이터 형식 우선 순위를 참조 하세요.

   이 규칙은 규칙 1에 따라 연결된 모든 데이터 형식과 우선 순위가 가장 큰 데이터 형식 간에 암시적 변환이 있는 경우에만 적용됩니다. 암시적 변환이 없으면 오류와 함께 데이터 형식 추론이 실패합니다. 예를 들어 쿼리 SELECT @p FROM t에서 데이터 형식은 동일하게 양수이므로 데이터 형식 @p 추론이 실패합니다. 예를 들어 int에서 xml로의 암시적 변환은 없습니다.

3. varchar(8000) 및 varchar(max)와 같은 두 개의 유사한 데이터 형식이 규칙 1에 따라 연결되면 더 작은 데이터 형식(varchar(8000))이 선택됩니다. nvarchar 및 varbinary 데이터 형식에 동일한 원칙이 적용됩니다.

4. 규칙 1을 위해 형식 추론 알고리즘은 특정 변환을 다른 변환보다 더 선호합니다. 최상의 순서에서 최악의 순서로 변환은 다음과 같습니다.

   1. 길이가 다른 동일한 기본 데이터 형식 간의 변환
   2. 동일한 데이터 형식의 고정 길이 버전과 가변 길이 버전 간 변환(예: char에서 varchar로).
   3. int 간 NULL 변환입니다.
   4. 다른 모든 변환입니다.

예를 들어 쿼리SELECT * FROM t WHERE [Col_varchar(30)] > @p의 경우 변환(a)이 가장 적합하기 때문에 varchar(8000)가 선택됩니다. 쿼리SELECT * FROM t WHERE [Col_char(30)] > @p의 경우 varchar(8000)는 형식(b) 변환을 유발하고 다른 선택(예: varchar(4000))으로 인해 형식(d) 변환이 발생하므로 계속 선택됩니다.

쿼리가 지정된 마지막 예제에서는 SELECT NULL + @p형식(c) 변환이 발생하므로 int가 선택 @p 됩니다.

사용 권한

@tsql 인수를 실행할 수 있는 권한이 필요합니다.

예제

다음 예에서는 선언되지 않은 @id 및 @name 매개 변수에 필요한 데이터 형식과 같은 정보를 반환합니다.

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

매개 변수를 @id 참조 @id 로 @params 제공하면 매개 변수가 결과 집합에서 생략되고 매개 변수만 @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';

관련 콘텐츠

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