We help IT Professionals succeed at work.
Get Started

T-SQL looping array?

988 Views
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)
                begin
                        SET @WhereState = @WhereState --(do nothing)
                end
        else
                begin
                        SET @WhereState = @WhereState + ' AND RB.BrochureID = ' + @BrochureID    
                end



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

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

                             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 + ' ) '

                  Else

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

                  End
   
                end


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.

Cheers,
Fracture
Comment
Watch Question
Commented:
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