30

How to loop through comma separated list in SQL? I have a list of ID's and I need to pass these ID's to a stored procedure. I CANNOT alter the stored procedure. I need to figure out how to execute the SP for each id. Give me some ideas, I can carry on from there.

Thanks.

Yuck
  • 47,217
  • 13
  • 101
  • 134
Virus
  • 2,957
  • 7
  • 28
  • 43
  • 2
    Do you want to call the stored procedure for EACH ID seperately? What language are you working outside of SQL? – n8wrl Apr 05 '12 at 15:40
  • If you can't alter the stored, why not call it multiple times? – Soader03 Apr 05 '12 at 15:40
  • SQL Server 2008-yes I want to call the stored procedure for each id as I cannot alter the SP itself. I have execute the SQL query. There is no other language used. – Virus Apr 05 '12 at 15:46
  • +1 for "Give me some ideas, I can carry on from there." – HLGEM Apr 05 '12 at 17:42

1 Answers1

89
declare @S varchar(20)
set @S = '1,2,3,4,5'

while len(@S) > 0
begin
  --print left(@S, charindex(',', @S+',')-1)
  exec YourSP left(@S, charindex(',', @S+',')-1)
  set @S = stuff(@S, 1, charindex(',', @S+','), '')
end

Try on SE Data: Walk the string

Cœur
  • 34,719
  • 24
  • 185
  • 251
Mikael Eriksson
  • 132,594
  • 21
  • 199
  • 273