I have the following query:
with cte as
(SELECT top 10 [1],[2]
FROM [tbl_B] where [2] > '2000-01-01' and Status_7 = 0 and Status_8 = 1
ORDER BY [2])
,
CTE1 AS
( select [1], row_number() over (order by [2]) as rn
from CTE
)
select [1] from CTE1 where rn = '10'
how can I put this into a variable to compare it to another query result? If i use set @123 = (above query) it gives errors.
with cte as
(
SELECT top 10 [1],[2]
FROM [tbl_B]
where [2] > '2000-01-01' and Status_7 = 0 and Status_8 = 1
ORDER BY [2]
)
,CTE1 AS
(
select [1], row_number() over (order by [2]) as rn
from CTE
)
select @123 = [1] from CTE1 where rn = '10'
SET @123 = will "explode" if there is more than a row, the SELECT @123 = will execute with no problems and put the value of the last row.with cte as
(SELECT top 10 [1],[2]
FROM [tbl_B] where [2] > '2000-01-01' and Status_7 = 0 and Status_8 = 1
ORDER BY [2])
,
CTE1 AS
( select [1], row_number() over (order by [2]) as rn
from CTE
)
select @123 = [1] from CTE1 where rn = '10'
You can use a table variable to store the CTE's result set. For example:
declare @table_var table (id int, col1 varchar(50));
; with CTE as
(
... your definition here ...
)
insert @table_var
(id, col1)
select id
, col1
from CTE
Comparing this with another set can be done with a full outer join:
select coalesce(t1.id, t2.id) as id
, coalesce(t1.col1, t2.col1) as col1
, case
when t1.id is null then 'Missing in t1'
when t2.id is null then 'Missing in t2'
when isnull(t1.col1,'') <> isnull(t2.col1,'') then 'Col1 is different'
else 'Identical'
end as Difference
from @table_var1 t1
full outer join
@table_var2 t2
on t1.id = t2.id
