lists/arrays in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

lists/arrays in sql

Author

Topic

IanKM
Starting Member

18 Posts

Posted - 2012-06-22 : 06:17:22

Hi there

I want to use a list in sql a bit like you might in .net and not sure if its possible

bascially what i want to do is something like this


Declare @array1 as decimal()
SET @array1 = (SELECT Data From Table1)

and then use it like 

SELECT something 
FROM Table2
WHERE Somethingelse IN (@array1)

The reason i want to do it like this is i need to reuse the table of data two or three times and would prefer not having to keep questioning the database

many thanks Ian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-22 : 07:02:02

In SQL you use tables like you would arrays and lists in other programming languages. If you query a table repeatedly its data will be stored in RAM just like an array or list would, so there's little penalty in querying it multiple times. You can make RAM residence a little more likely by using a table variable:

Declare @array1 as table(col decimal(10,2))
INSERT @array1(col) SELECT Data From Table1

SELECT something 
FROM Table2
WHERE Somethingelse IN (SELECT col FROM @array1)

-- Or...

SELECT something 
FROM Table2 A
INNER JOIN @array1 B ON
A.Somethingelse=B.col

IanKM
Starting Member

18 Posts

Posted - 2012-06-22 : 07:52:58

Oh mate that worked a blooming treat

thanks so much the report im just about to write is gonna work so much faster

Much respect

Ian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources