SQL Server 프로시저가 목록 선언

SQL Server 프로시저가 목록 선언

제 SQL 코드는 매우 간단합니다.다음과 같은 데이터베이스에서 일부 데이터를 선택하려고 합니다.

SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)

선택(변수, 목록, 배열 등) 전에 목록을 선언하고 선택 내부에서 다음과 같은 변수 이름만 사용하는 방법을 알고 싶습니다.

VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList

다음과 같이 변수를 임시 테이블로 선언할 수 있습니다.

declare @myList table (Id int)

그 말은 당신이 그것을 사용할 수 있다는 것을 의미합니다.insert값으로 채울 문:

insert into @myList values (1), (2), (5), (7), (10)

그럼 당신의select문은 다음 중 하나를 사용할 수 있습니다.in문:

select * from DBTable
where id in (select Id from @myList)

또는 다음과 같이 임시 테이블에 참여할 수 있습니다.

select *
from DBTable d
join @myList t on t.Id = d.Id

이와 같은 작업을 자주 수행하는 경우 다음과 같이 변수를 선언할 수 있도록 사용자 정의 테이블 유형을 정의할 수 있습니다.

declare @myList dbo.MyTableType

일반 쿼리에서는 불가능합니다.in절에는 쉼표로 구분된 목록을 포함하는 단일 값이 아니라 별도의 값이 필요합니다.하나의 솔루션은 동적 쿼리입니다.

declare @myList varchar(100)
set @myList = '1,2,5,7,10'
exec('select * from DBTable where id IN (' + @myList + ')')

전달된 값 목록을 테이블 값 매개 변수로 변환한 다음 이 목록에서 선택할 수 있습니다.

DECLARE @list NVARCHAR(MAX)
SET @list = '1,2,5,7,10';

DECLARE @pos INT
DECLARE @nextpos INT
DECLARE @valuelen INT
DECLARE @tbl TABLE (number int NOT NULL)

SELECT @pos = 0, @nextpos = 1;

WHILE @nextpos > 0
BEGIN
    SELECT @nextpos = charindex(',', @list, @pos + 1)
    SELECT @valuelen = CASE WHEN @nextpos > 0
                            THEN @nextpos
                            ELSE len(@list) + 1
                        END - @pos - 1
    INSERT @tbl (number)
        VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
    SELECT @pos = @nextpos;
END

SELECT * FROM DBTable WHERE id IN (SELECT number FROM @tbl);

이 예에서 '1,2,5,7,10'에 전달된 문자열은 쉼표로 구분되며 각 값은 다음과 같이 새 행으로 추가됩니다.@tbl테이블 변수그런 다음 표준 SQL을 사용하지 않도록 선택할 수 있습니다.

이 기능을 재사용하려는 경우 더 나아가 이 기능을 함수로 변환할 수 있습니다.

저는 항상 이런 상황에서 목록에 대한 테스트를 뒤집는 것이 더 쉽다는 것을 알았습니다.예를 들면...

SELECT 
    field0, field1, field2 
FROM 
    my_table 
WHERE 
    ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%' 

즉, 찾고 있는 값에 대해 복잡한 오매쉬가 필요하지 않습니다.

예를 들어, 만약 우리의 목록이('1,2,3')그런 다음 목록의 시작과 끝에 다음과 같이 쉼표를 추가합니다.',' + @mysearchlist + ','.

또한 찾고 있는 필드 값에 대해서도 동일한 작업을 수행하고 와일드카드를 추가합니다.'%,' + CAST(field3 AS VARCHAR) + ',%'(계속)%그리고,문자).

마지막으로 다음을 사용하여 두 가지를 테스트합니다.LIKE연산자:',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.

@Peter Monks의 대안.

'in' 문에 있는 숫자가 작고 고정되어 있는 경우.

DECLARE @var1 varchar(30), @var2 varchar(30), @var3  varchar(30);

SET @var1 = 'james';
SET @var2 = 'same';
SET @var3 = 'dogcat';

Select * FROM Database Where x in (@var1,@var2,@var3);

입력으로 쉼표로 구분된 문자열을 입력하고 쿼리에 적용하려면 다음과 같은 Function을 만들 수 있습니다.

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
    returns @temptable TABLE (items varchar(MAX))       
    as       
    begin      
        declare @idx int       
        declare @slice varchar(8000)       

        select @idx = 1       
            if len(@String)<1 or @String is null  return       

        while @idx!= 0       
        begin       
            set @idx = charindex(@Delimiter,@String)       
            if @idx!=0       
                set @slice = left(@String,@idx - 1)       
            else       
                set @slice = @String       

            if(len(@slice)>0)  
                insert into @temptable(Items) values(@slice)       

            set @String = right(@String,len(@String) - @idx)       
            if len(@String) = 0 break       
        end   
    return 
    end;

다음과 같이 사용할 수 있습니다.

Declare @Values VARCHAR(MAX);

set @Values ='1,2,5,7,10';
Select * from DBTable
    Where id  in (select items from [dbo].[Split] (@Values, ',') )

또는 쉼표로 구분된 문자열을 입력하지 않은 경우 시도할 수 있습니다.Table variableORTableType또는Temp tablelike: 목록을 사용하여 저장 프로시저에 삽입

언급URL : https://stackoverflow.com/questions/21160456/sql-server-procedure-declare-a-list