T-SQL: Parcourir une tableau de valeurs connues

Question

Voici mon scénario :

Disons que j'ai une procédure stockée dans laquelle j'ai besoin d'appeler une autre procédure stockée sur un ensemble d'identifiants spécifiques ; est-ce possible de le faire ?

c'est-à-dire au lieu d'avoir à faire ceci :

exec p_MyInnerProcedure 4
exec p_MyInnerProcedure 7
exec p_MyInnerProcedure 12
exec p_MyInnerProcedure 22
exec p_MyInnerProcedure 19

Faire quelque chose comme ceci :

*magie où je spécifie que ma liste contient 4,7,12,22,19*

DECLARE my_cursor CURSOR FAST_FORWARD FOR
*magie sélectionner*

OPEN my_cursor 
FETCH NEXT FROM my_cursor INTO @MyId
WHILE @@FETCH_STATUS = 0
BEGIN

exec p_MyInnerProcedure @MyId

FETCH NEXT FROM my_cursor INTO @MyId
END

Mon objectif principal ici est simplement la maintenabilité (facile à supprimer/ajouter des identifiants au fur et à mesure que l'entreprise évolue), pouvoir lister tous les identifiants sur une seule ligne... La performance ne devrait pas être un aussi grand problème

Answer 1

declare @ids table(idx int identité(1,1), id int)

insert into @ids (id)
    select 4 union
    select 7 union
    select 12 union
    select 22 union
    select 19

declare @i int
declare @cnt int

select @i = min(idx) - 1, @cnt = max(idx) from @ids

while @i < @cnt
begin
     select @i = @i + 1

     declare @id = select id from @ids where idx = @i

     exec p_MyInnerProcedure @id
end

Answer 2

Ce que je fais dans ce scénario est de créer une variable de table pour contenir les Ids.

  Déclarer @Ids Table (id integer primary Key not null)
  Insérer @Ids(id) valeurs (4),(7),(12),(22),(19)

-- (ou appeler une autre fonction de table pour générer cette table)

Ensuite, bouclez en fonction des lignes de cette table

  Déclarer @Id Entier
  Tant que existe (Sélectionner * De @Ids)
    Début
      Sélectionner @Id = Min(id) de @Ids
      exec p_MyInnerProcedure @Id 
      Supprimer de @Ids Où id = @Id
    Fin

ou...

  Déclarer @Id Entier = 0 -- en supposant que tous les Ids sont > 0
  Tant que existe (Sélectionner * De @Ids
                où id > @Id)
    Début
      Sélectionner @Id = Min(id) 
      de @Ids Où id > @Id
      exec p_MyInnerProcedure @Id 
    Fin

L'une ou l'autre des approches ci-dessus est bien plus rapide qu'un curseur (déclaré contre des tables utilisateur régulières). Les variables de table ont mauvaise réputation car lorsqu'elles sont utilisées de manière incorrecte, (pour des tables très larges avec un grand nombre de lignes) elles ne sont pas performantes. Mais si vous les utilisez uniquement pour contenir une valeur de clé ou un entier sur 4 octets, avec un index (comme dans ce cas), elles sont extrêmement rapides.

Answer 3

Utiliser une variable de curseur statique et une fonction split:

déclarer @comma_delimited_list en varchar(4000)
set @comma_delimited_list = '4,7,12,22,19'

déclarer @cursor en curseur
set @cursor = curseur statique pour
   select convert(int, value) as Id from string_split(@comma_delimited_list, ',')
déclarer @id en int
ouvrir @cursor
while 1=1 begin
  fetch next from @cursor into @id
  if @@fetch_status <> 0 break
  ....faire quelque chose....
end
-- pas strictement nécessaire avec les variables de curseur car elles sortiront du scope comme une variable normale
fermer @cursor
détruire @cursor

Les curseurs ont mauvaise réputation car les options par défaut lorsqu'ils sont déclarés contre des tables utilisateur peuvent générer beaucoup de surcharge.

Mais dans ce cas, la surcharge est assez minimale, moins que toute autre méthode ici. STATIC indique à SQL Server de matérialiser les résultats dans tempdb et ensuite itérer sur ceux-ci. Pour de petites listes comme celle-ci, c'est la solution optimale.

Answer 4

Vous pouvez essayer comme ci-dessous :

déclarer @list varchar(MAX), @i int
sélectionner @i=0, @list ='4,7,12,22,19,'

while( @i < LEN(@list))
begin
    déclarer @item varchar(MAX)
    SÉLECTION  @item = SUBSTRING(@list,  @i,CHARINDEX(',',@list,@i)-@i)
    sélectionner @item

     --faire votre travail ici avec @item 
     exec p_MyInnerProcedure @item 

    set @i = CHARINDEX(',',@list,@i)+1
    if(@i = 0) set @i = LEN(@list) 
end

Answer 5

Je utilise généralement l'approche suivante

DECLARE @calls TABLE (
    id INT IDENTITY(1,1)
    ,parameter INT
    )

INSERT INTO @calls
select parameter from some_table where some_condition -- ici vous remplissez vos paramètres

declare @i int
declare @n int
declare @myId int
select @i = min(id), @n = max(id) from @calls
while @i <= @n
begin
    select 
        @myId = parameter
    from 
        @calls
    where id = @i

        EXECUTE p_MyInnerProcedure @myId
    set @i = @i+1
end