What I am wanting to do is to create an array (or maybe I should just use a table for this?) and then iterate each element of the array

So rough pseudocode would be

--Create a string array

Array tulaney ('Fire', 'Ice', 'Water', 'Stone', 'Wood', 'Brick')

--Then iterate the array with a for each loop

foreach (string abcd in tulaney)

--do some processing

I would use a table for this.

What kind of processing are you doing?  You can almost always do this processing with a single statement (set based logic). 

A table (local table variable or table-valued parameter) is likely the right tool for the job.  Keep in mind that SQL Server is better optimized for set-based processing rather than iteration.  If "do some processing" can be done as a set-based operation, you will likely get the best performance but it's difficult to say for sure without more details.

Hi,

we dont use arrays and iterations.

Array = Table

Iteration = Join

DECLARE @Elements TABLE(Name varchar(100))

INSERT INTO @Elements (Name) VALUES ('Fire'), ('Ice'), ('Water'), ('Stone'), ('Wood'), ('Brick')

SELECT e.Name, ee.Name
FROM @Elements AS e
INNER JOIN @Elements AS ee ON e.Name <> ee.Name
Ice - Fire
Water - Fire
Stone - Fire
Wood - Fire
Brick - Fire
Fire - Ice
Water - Ice
...     ...

• Edited by Steelleg4 18 hours 15 minutes ago
• Proposed as answer by Naomi NModerator 16 hours 26 minutes ago

I would use a table for this.

What kind of processing are you doing?  You can almost always do this processing with a single statement (set based logic).&n

SQL Server is a "set based" query system.  You should not need to loop through an array to get that data.

• Proposed as answer by Naomi NModerator 16 hours 26 minutes ago

SQL Server is a "set based" query system.  You should not need to loop through an array to get that data.

@Tom how would you recommend I go about to pull this data?

Hello,

Please consider the following article:

http://www.sommarskog.se/arrays-in-sql.html

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Proposed as answer by Naomi NModerator 16 hours 26 minutes ago
• Marked as answer by HotWildWetSlides 16 hours 22 minutes ago

• Proposed as answer by Naomi NModerator 16 hours 26 minutes ago

Hi,

we dont use arrays and iterations.

Array = Table

Iteration = Join

DECLARE @Elements TABLE(Name varchar(100))

INSERT INTO @Elements (Name) VALUES ('Fire'), ('Ice'), ('Water'), ('Stone'), ('Wood'), ('Brick')

SELECT e.Name, ee.Name
FROM @Elements AS e
INNER JOIN @Elements AS ee ON e.Name <> ee.Name
Ice - Fire
Water - Fire
Stone - Fire
Wood - Fire
Brick - Fire
Fire - Ice
Water - Ice
...     ...

• Edited by Steelleg4 Friday, September 11, 2015 12:47 PM
• Proposed as answer by Naomi NModerator Friday, September 11, 2015 2:37 PM

SQL Server is a "set based" query system.  You should not need to loop through an array to get that data.

• Proposed as answer by Naomi NModerator Friday, September 11, 2015 2:36 PM

Hello,

Please consider the following article:

http://www.sommarskog.se/arrays-in-sql.html

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Proposed as answer by Naomi NModerator Friday, September 11, 2015 2:37 PM
• Marked as answer by HotWildWetSlides Friday, September 11, 2015 2:40 PM

• Proposed as answer by Naomi NModerator Friday, September 11, 2015 2:36 PM

Your description is very vague, but you would do something like:

DECLARE @locations TABLE (location varchar(20) NOT NULL PRIMARY KEY)
INSERT @locations (location)
   VALUES ('Fire'), ('Ice'), ('Water'), ('Stone'), ('Wood'), ('Brick')

SELECT ...
FROM   tbl
CROSS JOIN @locations

If the intent is to process tbl once for each location.

Read a book on RDBMS and pay attention to the parts about  First Normal Form (1NF). All data is modeled as scalar values in the columns of a table. Arrays are structured data, not 1NF. We do not use them! Look up Codd's 12 Rules. 

SQL is a declarative language. We do not iterate or loop; that was procedural programming. Your mindset is still locked back in FORTRAN I and the 1960's. Our "unit of work" is an entire set of data, with operations done on the whole set all at once. 

One way is to build a view  and use it with other SQL statements: