Définir la variable à utiliser avec l'opérateur IN (T-SQL)

Question

J'ai une requête Transact-SQL qui utilise l'opérateur IN. Quelque chose comme ça:

 select * from myTable where myColumn in (1,2,3,4)
 

Existe-t-il un moyen de définir une variable pour contenir la liste complète "(1,2,3,4)"? Comment devrais-je le définir?

 declare @myList {data type}
set @myList = (1,2,3,4)
select * from myTable where myColumn in @myList
 

Answer 1

DECLARE @MyList TABLE (Value INT)
INSERT INTO @MyList VALUES (1)
INSERT INTO @MyList VALUES (2)
INSERT INTO @MyList VALUES (3)
INSERT INTO @MyList VALUES (4)

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

Answer 2

DECLARE @mylist TABLE (Id int)
INSERT INTO @mylist
SELECT id FROM (VALUES (1),(2),(3),(4),(5)) AS tbl(id)

SELECT * FROM Mytable WHERE theColumn IN (select id from @mylist)

Answer 3

Il y a deux façons de s'attaquer dynamique csv listes pour TSQL requêtes:

1. À l'aide d'une sélection interne

   SELECT * from myTable where macolonne in (SELECT id from myIdTable where id > 10)

2. L'aide dynamique concaténées TSQL

   DECLARE @sql de type varchar(max)
   declare @liste varchar(256)
   sélectionnez @liste = '1,2,3'
   SÉLECTIONNEZ @sql = 'SELECT * from myTable where macolonne ("+ @+ ')'

   exec sp_executeSQL @sql

Une possible troisième option est un tableau de variables. Si vous avez SQl Server 2005, vous pouvez utiliser une variable de table. Si votre sur Sql Server 2008, vous pouvez même passer toute la table des variables en tant que paramètre de procédures stockées et de les utiliser dans une jointure ou une sous-sélection dans la clause in.

3.

DECLARE @list TABLE (Id INT)

INSERT INTO @list(Id)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4


SELECT
    * 
FROM 
    myTable
    JOIN @list l ON myTable.myColumn = l.Id

SELECT
    * 
FROM 
    myTable
WHERE
    myColumn IN (SELECT Id FROM @list)

Answer 4

Utilisez une fonction comme celle-ci:

 CREATE function [dbo].[list_to_table] (@list varchar(4000))
returns @tab table (item varchar(100))
begin

if CHARINDEX(',',@list) = 0 or CHARINDEX(',',@list) is null
begin
    insert into @tab (item) values (@list);
    return;
end


declare @c_pos int;
declare @n_pos int;
declare @l_pos int;

set @c_pos = 0;
set @n_pos = CHARINDEX(',',@list,@c_pos);

while @n_pos > 0
begin
    insert into @tab (item) values (SUBSTRING(@list,@c_pos+1,@n_pos - @c_pos-1));
    set @c_pos = @n_pos;
    set @l_pos = @n_pos;
    set @n_pos = CHARINDEX(',',@list,@c_pos+1);
end;

insert into @tab (item) values (SUBSTRING(@list,@l_pos+1,4000));

return;
end;
 

Au lieu d'utiliser like, vous créez une jointure interne avec la table renvoyée par la fonction:

 select * from table_1 where id in ('a','b','c')
 

devient

 select * from table_1 a inner join [dbo].[list_to_table] ('a,b,c') b on (a.id = b.item)
 

Dans une table d'enregistrement 1M non indexée, la seconde version prenait environ la moitié du temps ...

à votre santé

Answer 5

Non, il n'y a pas un tel type. Mais il y a quelques choix:

• Requêtes générées dynamiquement (sp_executesql)
• Tables temporaires
• Variables de type table (élément le plus proche d'une liste)
• Créez une chaîne XML, puis convertissez-la en une table avec les fonctions XML (vraiment gênant et rond-point, sauf si vous avez un XML pour commencer)

Aucune d'entre elles n'est vraiment élégante, mais c'est la meilleure qui soit.