Looping through Array Values in T-SQL

trufla · April 13th, 2007, 04:32 AM

Hiya, Newbie here!

I have a function that will spilt comma separated values and output them as one column of values called Item.

Code:

DECLARE @RecipientID INT
DECLARE @ArrayValues VARCHAR (255) 
SET @ArrayValues = 'One, Two, Three and Four'
SELECT * FROM function(@ArrayValues, ',') as ArraySplit

How can I loop through each value in ArraySplit and compare that value to values from a specific column in a table, and then pull out other columns from that table.

For example:

Code:

While
    SELECT * FROM function(@ArrayValues, ',') as ArraySplit
Begin
    Select TableName.ID, TableName.Name
    Where TableName.TableColumn = ArraySpilt
    Print 'It worked!'
Else
   Print 'It didn't work!'
End

I am not sure of the syntax, and I am hoping my logic for this task is correct.

Any help appreciated!

happygv · April 13th, 2007, 04:57 AM

You can store that into a table (or temporary table) and do a join with the other table for comparision. And make sure you delete the table that you created during this process.

You can use something like this.

Code:

SELECT * INTO SomeTable FROM function(@ArrayValues, ',') as ArraySplit

Hope that helps.

_________________________
- Vijay G
Strive for Perfection

trufla · April 13th, 2007, 05:34 AM

Aaah cheers! Could I trouble you for more help?

I have tried doing the JOIN to compare the Array value with entries in another table

Code:

DECLARE @RecipientID INT
DECLARE @ArrayValues VARCHAR (255) 
SET @ArrayValues = 'One, Two, Three and four'

SELECT * INTO CategorySelection FROM function(@ArrayValues, ',') as ArraySplit

Select * FROM Categories
JOIN Categories on Categories.[Name] = ArraySplit.Item

I get this error

Code:

Tables or functions 'Categories' and 'Categories' have the same exposed names. Use correlation names to distinguish them.

happygv · April 13th, 2007, 05:39 AM

Is the join done correctly? Should be something like this.

Code:

Select * FROM Categories
JOIN CategorySelection on Categories.[Name] = CategorySelection.COLUMNNAME

_________________________
- Vijay G
Strive for Perfection

trufla · April 13th, 2007, 06:14 AM

Aah I see! Ta very much!