32

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
  • 49,664
  • 13
  • 105
  • 135
Virus
  • 3,215
  • 7
  • 29
  • 46
  • 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

91
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
  • 37,241
  • 25
  • 195
  • 267
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281