Процедура SQL Server объявляет список

17

Мой код SQL довольно прост. Я пытаюсь выбрать некоторые данные из базы данных следующим образом:

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

Я хочу знать, как объявить список перед выбором (в переменной, списке, массиве или что-то в этом роде), и внутри select только используйте имя переменной, примерно так:

VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
    
задан Alex Doro 16.01.2014 в 12:25
источник

5 ответов

35

Вы можете объявить переменную как временную таблицу следующим образом:

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
    
ответ дан Peter Monks 16.01.2014 в 12:43
источник
6

Это невозможно при обычном запросе, так как предложение in требует отдельных значений, а не одного значения, содержащего список, разделенный запятыми. Одним из решений будет динамический запрос

declare @myList varchar(100)
set @myList = '(1,2,5,7,10)'
exec('select * from DBTable where id IN ' + @myList)
    
ответ дан juergen d 16.01.2014 в 12:27
источник
0

Вы можете преобразовать список переданных значений в параметр, оцененный в таблице, а затем выбрать в этом списке

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.

Если вы намерены повторно использовать эту функциональность, вы можете пойти дальше и преобразовать ее в функцию.

    
ответ дан Jonathan 16.01.2014 в 12:45
источник
0

Если вы хотите, чтобы вводимая строка, разделенная запятыми, была введена как вход & amp; примените в запросе то, что вы можете сделать функцию вроде:

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 variable ИЛИ TableType или Temp table like: INSERT с использованием LIST в хранимой процедуре

    
ответ дан Pranav Singh 16.01.2014 в 12:53
источник
0

Мне всегда было легче инвертировать тест против списка в таких ситуациях. Например ...

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

Это означает, что для значений, которые вы ищете, нет сложного mish-mash.

В качестве примера, если наш список был ('1,2,3') , тогда мы добавляем запятую в начало и конец нашего списка: ',' + @mysearchlist + ',' .

Мы также делаем то же самое для значения поля, которое мы ищем, и добавляем подстановочные знаки: '%,' + CAST(field3 AS VARCHAR) + ',%' (обратите внимание на символы % и , ).

Наконец, мы тестируем два, используя оператор LIKE : ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%' .

    
ответ дан Paul 16.01.2014 в 13:06
источник