Using Arrays in SQL

Question

I have this sql statement:

DECLARE @option_id INT;

SELECT DISTINCT product_options.option_id
INTO   @option_id
FROM   product_options,
       product_options_descriptions
WHERE  product_options.product_id = '31288'
       AND product_options_descriptions.option_name = "Color";

SELECT @option_id;

Which works fine. What I would like to do is is use @option_id to select multiple ids into an array. However as far as I can tell DECLARE @var only works with single values and I can't find any information on array datatypes in sql.

In outline I want to do the following

declare @option_id;
SELECT @option_id;
DECLARE @id_array;
SELECT into @id_array WHERE id = @option_id;
DECLARE @return_array;
FOREACH @id IN @id_array {
@return_array[] = SELECT value FROM column where id = @id
}
SELECT @return_array

Does anyone know where I can find tutorials ect to achieve this?

Aghilas Yakoub · Accepted Answer · 2012-08-29 15:19:29Z

1

You can use temporary table , table prefixed with #

CREATE TABLE #YourTable (  
  YourColumn int 
)

LInk : http://www.sqlteam.com/article/temporary-tables

For mysql syntax : http://dev.mysql.com/doc/refman/5.1/en/create-table.html

edited Aug 29, 2012 at 15:19

answered Aug 29, 2012 at 14:19

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tracer tong Over a year ago

and then I can run standard sql commands on it? Also would I need to drop the table afterwards or does this count as a tempory table?

Aghilas Yakoub Over a year ago

Yes you can run your treatments normally, and drop your table in the end of treatment

tracer tong Over a year ago

Cheers, that is what I wanted, Big green tick for you

Aghilas Yakoub Over a year ago

I'am happy to help you tracer tong Thank you

eggyal Over a year ago

That syntax (and article) is for sql-server. For mysql, you want CREATE TEMPORARY TABLE ....

Collectives™ on Stack Overflow

Using Arrays in SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related