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
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
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.
what are you attempting to do? your requirements are vague. vague requirements = vague answers and guesses
That's the most palatable. Are the semicolons and the 'Go' necessary?
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.