sp_describe_undeclared_parameters(Transact-SQL)

  • 아티클

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

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

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

인수

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

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

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

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

반환 코드 값

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

결과 집합

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

열 이름

데이터 형식

설명

parameter_ordinal

int NOT NULL

결과 집합에서 매개 변수의 서수 위치를 포함합니다. 첫 번째 매개 변수의 위치가 1로 지정됩니다.

name

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

데이터 형식 이름을 포함합니다. 매개 변수의 데이터 형식에 지정된 인수(length, precision, scale 등)를 포함합니다. 데이터 형식이 사용자 정의 별칭 형식인 경우 기본 시스템 형식이 여기에 지정됩니다. 데이터 형식이 CLR 사용자 정의 데이터 형식인 경우 이 열에 NULL이 반환됩니다. 매개 변수의 형식을 추론할 수 없는 경우 NULL이 반환됩니다.

suggested_max_length

smallint NOT NULL

max_length 열에 대한 설명은 sys.columns를 참조하십시오.

suggested_precision

tinyint NOT NULL

precision 열에 대한 설명은 sys.columns를 참조하십시오.

suggested_scale

tinyint NOT NULL

scale 열에 대한 설명은 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

bit NOT NULL

반환할 데이터 형식이 XML이고 해당 형식이 XML 문서임이 보장될 경우 1을 반환합니다. 그렇지 않으면 0을 반환합니다.

suggested_is_case_sensitive

bit NOT NULL

열이 대/소문자를 구분하는 문자열 형식인 경우 1을 반환하고 그렇지 않으면 0을 반환합니다.

suggested_is_fixed_length_clr_type

bit NOT NULL

열이 고정 길이 CLR 형식인 경우 1을 반환하고 그렇지 않으면 0을 반환합니다.

suggested_is_input

bit NOT NULL

매개 변수가 대입의 왼쪽이 아닌 다른 곳에 사용되는 경우 1을 반환합니다. 그렇지 않으면 0을 반환합니다.

suggested_is_output

bit 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에 선언된 매개 변수만 있는 경우 이 프로시저는 빈 결과 집합을 반환합니다.

매개 변수 선택 알고리즘

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

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) - 다른 숫자 또는 10진수 데이터 형식은 고려되지 않음

    • 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

    이 경우 E(@p)는 Col_Int + @p이고, TT(@p)는 int입니다. 따라서 생성되는 암시적 변환이 없으므로 @p에 대해 int가 선택됩니다. 다른 데이터 형식은 하나 이상의 암시적 변환을 생성합니다.

  2. 변환 수가 가장 적은 데이터 형식에 여러 데이터 형식이 연결된 경우 우선 순위가 가장 높은 데이터 형식이 사용됩니다. 예를 들면 다음과 같습니다.

    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로의 암시적 변환이 없습니다.

  3. 유사한 데이터 형식 두 개(예: varchar(8000)과 varchar(max))가 규칙 1에 따라 연결된 경우 보다 작은 데이터 형식(varchar(8000))이 선택됩니다. nvarchar 및 varbinary 데이터 형식에도 동일한 원칙이 적용됩니다.

  4. 규칙 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) 유형의 변환이 발생하므로 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 매개 변수가 @params 참조로 제공된 경우에는 @id 매개 변수가 결과 집합에서 생략되고 @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_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)