sp_describe_undeclared_parameters(Transact-SQL)

아티클
08/21/2023

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

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

Transact-SQL 구문 표기 규칙

Syntax

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

참고 항목

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

인수

[ @tsql = ] 'Transact-SQL\_batch' 하나 이상의 Transact-SQL 문입니다. Transact-SQL_batch nvarchar(n) 또는 nvarchar(max)일 수 있습니다.

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

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

Datatype
매개 변수의 데이터 형식입니다.

반환 코드 값

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

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

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

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

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

3단계

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

단순 공제

E(@p) = @p 및 TT(@p)가 있는 경우, 즉 @p 2단계 시작 부분에 나열된 식 중 하나에 대한 인수인 경우 형식 추론 알고리즘은 @p 데이터 형식을 TT(@p)로 추론합니다. 예시:
```
SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3)  
```
@p1, @p2 및 @p3 대한 데이터 형식은 각각 c1의 데이터 형식, dbo.tbl의 반환 데이터 형식 및 dbo.tbl의 매개 변수 데이터 형식입니다.

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

선택 조건

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

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

예시:
```
SELECT * FROM t WHERE Col_Int = Col_Int + @p  
```
이 경우 E(@p)가 Col_Int + @p TT(@p)가 int입니다. int는 암시적 변환을 생성하지 않으므로 @p 대해 선택됩니다. 다른 데이터 형식 선택은 하나 이상의 암시적 변환을 생성합니다.
여러 데이터 형식이 가장 적은 수의 변환에 연결된 경우 우선 순위가 더 큰 데이터 형식이 사용됩니다. 예를 들면 다음과 같습니다.
```
SELECT * FROM t WHERE Col_Int = Col_smallint + @p  
```
이 경우 int 및 smallint 는 하나의 변환을 생성합니다. 다른 모든 데이터 형식은 둘 이상의 변환을 생성합니다. int가 smallint보다 우선하기 때문에 int는 @p 사용됩니다. 데이터 형식 우선 순위에 대한 자세한 내용은 데이터 형식 우선 순위(Transact-SQL)를 참조하세요.

이 규칙은 규칙 1에 따라 연결된 모든 데이터 형식과 우선 순위가 가장 큰 데이터 형식 간에 암시적 변환이 있는 경우에만 적용됩니다. 암시적 변환이 없으면 오류와 함께 데이터 형식 추론이 실패합니다. 예를 들어 쿼리 SELECT @p FROM t에서 데이터 형식 추론은 @p 모든 데이터 형식이 똑같이 양수이므로 실패합니다. 예를 들어 int에서 xml로의 암시적 변환은 없습니다.
varchar(8000) 및 varchar(max)와 같은 두 개의 유사한 데이터 형식이 규칙 1에 따라 연결되면 더 작은 데이터 형식(varchar(8000))이 선택됩니다. nvarchar 및 varbinary 데이터 형식에 동일한 원칙이 적용됩니다.
규칙 1을 위해 형식 추론 알고리즘은 특정 변환을 다른 변환보다 더 선호합니다. 최상의 순서에서 최악의 순서로 변환은 다음과 같습니다.
1. 길이가 다른 동일한 기본 데이터 형식 간의 변환
2. 동일한 데이터 형식의 고정 길이 버전과 가변 길이 버전 간 변환(예: char에서 varchar로).
3. NULL과 int 간의 변환입니다.
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) 변환이 발생하므로 @p int가 선택됩니다.

사용 권한

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

예제

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

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 설명됩니다.

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_undeclared_parameters(Transact-SQL)

Syntax

인수

반환 코드 값

결과 집합

설명

매개 변수 선택 알고리즘

선택 조건

사용 권한

예제

참고 항목

피드백

피드백

추가 리소스