CODEWITHSUNDEEP
sql
nadG
nadG

Reputation: 167

How to store result of sql query in variable?

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.

Upvotes: 2

Views: 5391

Answers (3)

Andomar
Andomar

Reputation: 238048

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

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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'

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

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'

Upvotes: 0

Related Questions