Procedimento do servidor de SQL declarar uma lista
SELECT * FROM DBTable
WHERE id IN (1,2,5,7,10)
quero saber como declarar a lista antes da selecção (numa variável, lista, lista ou algo do género) e dentro da selecção usar apenas o nome da variável, algo do género:
VAR myList = "(1,2,5,7,10)"
SELECT * FROM DBTable
WHERE id IN myList
6 answers
Pode declarar uma variável como uma tabela temporária como esta:
declare @myList table (Id int)
O que significa que pode usar a declaração insert para a povoar com valores:
insert into @myList values (1), (2), (5), (7), (10)
Então a sua declaração select pode usar a declaração in:
select * from DBTable
where id in (select Id from @myList)
Ou podes juntar - te à mesa temporária assim:
select *
from DBTable d
join @myList t on t.Id = d.Id
E se fizer muito uma coisa destas, então poderá considerar definir um tipo de tabela definido pelo utilizador para que possa então declarar a sua variável como isto:
declare @myList dbo.MyTableType
Isso não é possível com uma consulta normal uma vez que a cláusula in necessita de valores separados e não de um único valor que contenha uma lista separada por vírgulas. Uma solução seria uma consulta dinâmica
declare @myList varchar(100)
set @myList = '(1,2,5,7,10)'
exec('select * from DBTable where id IN ' + @myList)
SELECT
field0, field1, field2
FROM
my_table
WHERE
',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'
Como exemplo, se a nossa lista era ('1,2,3'), então adicionamos uma vírgula ao início e ao fim da nossa lista como assim: ',' + @mysearchlist + ','.
Nós também fazemos o mesmo para o valor do campo que estamos à procura e adicionamos wildcards: '%,' + CAST(field3 AS VARCHAR) + ',%' (note o % e o , caracter).
LIKE: ',' + @mysearchlist + ',' LIKE '%,' + CAST(field3 AS VARCHAR) + ',%'.
Se o número na declaração " in " for pequeno e fixo.
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);
Você pode converter a lista de valores passados para um parâmetro de valores de tabela e, em seguida, selecionar contra esta lista
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);
Neste exemplo, o texto passou '1,2,5,7,10' é dividido pelas vírgulas e cada valor é adicionado como uma nova linha dentro da variável @tbl quadro. Isto pode então ser selecionado contra o uso de SQL padrão.
Se pretende reutilizar esta funcionalidade, pode ir mais longe e converter isto numa função.
Se quiser um texto separado por vírgulas de entrada como entrada e aplicar na pesquisa nisso, então poderá fazer uma função como:
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;
Podes usá-lo como:
Declare @Values VARCHAR(MAX);
set @Values ='1,2,5,7,10';
Select * from DBTable
Where id in (select items from [dbo].[Split] (@Values, ',') )
Em alternativa, se não tiver um texto separado por vírgulas como entrada, pode tentar Table variable ou TableType ou Temp table como: inserir usando a lista no procedimento armazenado