What is the way to store tables/arrays in variables?

1

Hello

I know the DECLARE @VARIABLE int = 1, but how do I store arrays/tables in variables to be used in subsequent SELECT statements?

I tried:
With data1 as (SELECT *)
With data2 as (SELECT *)
SELECT *
FROM data1
LEFT JOIN data2

but it did not work

Thanks

2

to declare a cte, the next statement must include it, otherwise it's out of scope. You're query has 2 CTEs, so it doesn;t work. You'll need to nest them to get it to work

With data1 as (SELECT * from tablea),
data2 as (SELECT * from tableb)

SELECT *
FROM data1
LEFT JOIN data2

1 Like
3

Or - you can use table variables, temp tables, views or derived tables:

Create View data1 As Select * From tablea;
Go
Create View data2 As Select * From tableb;
Go

Then use the views:

Select *
From data1 d1
Join data2 d2 On ...
Where ...

Declare @data1 As table (col1 int, col2 varchar(30));
 Insert Into @data1 (col1, col2)
 Select Col1, Col2 From someTable;

Declare @data2 As table (col1 int, col2 varchar(30));
 Insert Into @data1 (col1, col2)
 Select Col1, Col2 From someOtherTable;

Select *
From @data1 d1
Join @data2 d2 On ...
Where ...

Or - common-table expressions (as @mike01 outlined) - or derived tables:

Select *
From (Select * From data1) d1
Join (Select * From data2) d2 On ...
Where ...

SQL Server does not have arrays - we have tables - which is much more powerful than an array.

1 Like
4

what are you attempting to do? your requirements are vague. vague requirements = vague answers and guesses

5

That's the most palatable. Are the semicolons and the 'Go' necessary?

6

I don't understand your question - the semi-colon is a statement terminator and although it is not required in all statements, it is required in certain cases. It is a good habit to get into...

The Go is a batch terminator - and is required in a script where you have multiple statements. So if you had a script to build multiple views it would be required.

If you are thinking you can 'define' a view in a stored procedure then use it later - that is not the purpose of views. A view would be used when you have a defined query that is used in multiple other queries (procedures) - or where you want to give a user access to a subset of rows or columns. It is not a technique for creating 'sub-queries' in a procedure.

If you want to define a sub-query in a stored procedure and use it in later code - then you want either a temp table, table variable or common table expression.