Variable SQL pour contenir une liste d'entiers

Question

J'essaie de déboguer les rapports SQL de quelqu'un d'autre et j'ai placé la requête de rapports sous-jacente dans une fenêtre de requête de SQL 2012.

L'un des paramètres demandés par le rapport est une liste d'entiers. Ceci est réalisé sur le rapport par le biais d'une liste déroulante à sélection multiple. La requête sous-jacente du rapport utilise cette liste de nombres entiers dans le champ where clause, par exemple

select *
from TabA
where TabA.ID in (@listOfIDs)

Je ne veux pas modifier la requête que je suis en train de déboguer mais je n'arrive pas à trouver comment créer une variable sur le serveur SQL qui puisse contenir ce type de données pour la tester.

par exemple

declare @listOfIDs int
set listOfIDs  = 1,2,3,4

Il n'y a pas de type de données qui puisse contenir une liste d'entiers, alors comment puis-je exécuter la requête du rapport sur mon serveur SQL avec les mêmes valeurs que le rapport ?

Answer 1

Variable du tableau

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);    

select *
from TabA
where TabA.ID in (select id from @listOfIDs)

o

declare @listOfIDs varchar(1000);
SET @listOfIDs = ',1,2,3,'; --in this solution need put coma on begin and end

select *
from TabA
where charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0

Answer 2

En supposant que la variable soit quelque chose de l'ordre de :

CREATE TYPE [dbo].[IntList] AS TABLE(
[Value] [int] NOT NULL
)

Et la procédure stockée l'utilise sous cette forme :

ALTER Procedure [dbo].[GetFooByIds]
    @Ids [IntList] ReadOnly
As 

Vous pouvez créer la liste IntList et appeler la procédure comme suit :

Declare @IDs IntList;
Insert Into @IDs Select Id From dbo.{TableThatHasIds}
Where Id In (111, 222, 333, 444)
Exec [dbo].[GetFooByIds] @IDs

Ou si vous fournissez vous-même la liste IntList

DECLARE @listOfIDs dbo.IntList
INSERT INTO @listofIDs VALUES (1),(35),(118);

Answer 3

Vous avez raison, il n'existe pas de type de données dans SQL-Server qui puisse contenir une liste d'entiers. Mais ce que vous pouvez faire est de stocker une liste d'entiers comme une chaîne de caractères.

DECLARE @listOfIDs varchar(8000);
SET @listOfIDs = '1,2,3,4';

Vous pouvez ensuite diviser la chaîne en valeurs entières distinctes et les placer dans un tableau. Votre procédure peut déjà le faire.

Vous pouvez également utiliser une requête dynamique pour obtenir le même résultat :

DECLARE @SQL nvarchar(8000);

SET @SQL = 'SELECT * FROM TabA WHERE TabA.ID IN (' + @listOfIDs + ')';
EXECUTE (@SQL);

Answer 4

Pour SQL Server 2016+ et Azure SQL Database, la fonction STRING_SPLIT a été ajoutée et constitue une solution parfaite à ce problème. Voici la documentation : https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Voici un exemple :

/*List of ids in a comma delimited string
  Note: the ') WAITFOR DELAY ''00:00:02''' is a way to verify that your script 
        doesn't allow for SQL injection*/
DECLARE @listOfIds VARCHAR(MAX) = '1,3,a,10.1,) WAITFOR DELAY ''00:00:02''';

--Make sure the temp table was dropped before trying to create it
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable;

--Create example reference table
CREATE TABLE #MyTable
([Id] INT NOT NULL);

--Populate the reference table
DECLARE @i INT = 1;
WHILE(@i <= 10)
BEGIN
    INSERT INTO #MyTable
    SELECT @i;

    SET @i = @i + 1;
END

/*Find all the values
  Note: I silently ignore the values that are not integers*/
SELECT t.[Id]
FROM #MyTable as t
    INNER JOIN 
        (SELECT value as [Id] 
        FROM STRING_SPLIT(@listOfIds, ',')
        WHERE ISNUMERIC(value) = 1 /*Make sure it is numeric*/
            AND ROUND(value,0) = value /*Make sure it is an integer*/) as ids
    ON t.[Id] = ids.[Id];

--Clean-up
DROP TABLE #MyTable;

Le résultat de la requête est 1,3

Answer 5

En fin de compte, je suis arrivé à la conclusion que sans modifier le fonctionnement de la requête, je ne pouvais pas stocker les valeurs dans des variables. J'ai utilisé SQL profiler pour attraper les valeurs et je les ai codées en dur dans la requête pour voir comment cela fonctionnait. Il y avait 18 de ces tableaux d'entiers et certains contenaient plus de 30 éléments.

Je pense qu'il est nécessaire que MS/SQL introduise quelques types de données supplémentaires dans le langage. Les tableaux sont assez courants et je ne vois pas pourquoi on ne pourrait pas les utiliser dans une procédure stockée.