T-SQL looping array?

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