How to split a comma separated string and loop it's values in SQL Server

How to split a comma separated string and loop it's values in SQL Server

Here is a simple T-SQL script for SQL Server that will split a comma separated string and loop on the values.
It's a simple way to create a array/list of things in SQL Server, and then do something on the values of the list.
These values can be anything: table names, stored procedures, query...

Application note:  the value list string must end with a comma ","

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)

SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,'

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    --SELECT @pos, @len, @value /*this is here for debugging*/
        
    PRINT @value
    --Here is you value
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE
    --DO YOUR STUFF HERE

    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

The output of this script is:

    aa
    bb
    cc
    f
    sduygfdctys
    w
    e
    r
    t
    sd sdf sdf
    yyy yyy yy

SQL Code colored with this syntax highlighter

Contrasting Three Projects
Three projects stand out in my memory as vivid examples of how dramatically domain design
practice can affect development results. Although all three projects delivered useful software, only
one achieved its ambitious objectives and produced complex software that continued to evolve to
meet the ongoing needs of the organization.
I watched one project get out of the gate fast, by delivering a useful, simple Web-based trading
system. Developers were flying by the seat of their pants, but this didn't hinder them because
simple software can be written with little attention to design. As a result of this initial success,
expectations for future development were sky-high. That is when I was asked to work on the
second version. When I took a close look, I saw that they lacked a domain model, or even a
common language on the project, and were saddled with an unstructured design. The project
leaders did not agree with my assessment, and I declined the job. A year later, the team found
itself bogged down and unable to deliver a second version. Although their use of technology was
not exemplary, it was the business logic that over-came them. Their first release had ossified
prematurely into a high-maintenance legacy.
Lifting this ceiling on complexity calls for a more serious approach to the design of domain logic.
Early in my career, I was fortunate to end up on a project that did emphasize domain design. This
project, in a domain at least as complex as the first one, also started with a modest initial success,
delivering a simple application for institutional traders. But in this case, the initial delivery was
followed up with successive accelerations of development. Each iteration opened exciting new
options for integrating and elaborating the functionality of the previous release. The team was able
to respond to the needs of the traders with flexibility and expanding capability. This upward
trajectory was directly attributable to an incisive domain model, repeatedly refined and expressed
in code. As the team gained new insight into the domain, the model deepened. The quality of
communication improved not only among developers but also between developers and domain
experts, and the design—far from imposing an ever-heavier maintenance burden—became easier
to modify and extend.
Unfortunately, projects don't arrive at such a virtuous cycle just by taking models seriously. One
project from my past started with lofty aspirations to build a global enterprise system based on a
domain model, but after years of disappointment, it lowered its sights and settled into
conventionality. The team had good tools and a good understanding of the business, and it gave
careful attention to modeling. But a poorly chosen separation of developer roles disconnected
modeling from implementation, so that the design did not reflect the deep analysis that was going
on. In any case, the design of detailed business objects was not rigorous enough to support
combining them in elaborate applications. Repeated iteration produced no improvement in the
code, due to uneven skill levels among developers, who had no awareness of the informal body of
style and technique for creating model-based objects that also function as practical, running
software. As months rolled by, development work became mired in complexity and the team lost
its cohesive vision of the system. After years of effort, the project did produce modest, useful
software, but the team had given up its early ambitions along with the model focus.

Story of Eric Evans from his book http://dddcommunity.org/book/evans_2003/