We help IT Professionals succeed at work.
Get Started

T-SQL looping array?

Last Modified: 2012-05-08
Hi all experts,

I think this should be relatively easy however i cant seem to find any examples online for the syntax and the clock is ticking..

I have a sql query that builds the query as a string then i execute it at the end. I am passing in a ID field that could be 1 value or mulitple values. For 1 value this works fine, however if multiple values (coma seperated) i need to loop through and build the string appropriately.

Here is the part of the code that im looking at -

        If (@BrochureID=0)
                        SET @WhereState = @WhereState --(do nothing)
                        SET @WhereState = @WhereState + ' AND RB.BrochureID = ' + @BrochureID    

So if multiple values it needs to do something like this? -

        If (@BrochureID=0)
                        SET @WhereState = @WhereState --(do nothing)

                             IF BrochureID has mulitple values...

                                    Split by coma's into array

                              SET @WhereState = @WhereState + ' AND ( '

                              --First Item
                              SET @WhereState = @WhereState + ' RB.BrochureID = ' + @BrochureID

                              Loop through array

                                    --Next Item
                                    SET @WhereState = @WhereState + ' OR RB.BrochureID = ' + @BrochureID
                              End Loop

                              SET @WhereState = @WhereState + ' ) '


                        SET @WhereState = @WhereState + ' AND RB.BrochureID = ' + @BrochureID


I think that is close to the logic? There is probably an easier way to do the same thing. If someone could convert my Pseudocode into the correct syntax or if there is a better way of doing it maybe write an example that would be great.

Watch Question
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE