Temporal Table result to variable doesn't work

Question

Im traying to set the result of a temporal table to a variable doing this im i doing somthig wrong??

WITH Consolidado_B (CANTIDAD,CALIDAD) AS(
    select 
        SUM(cbs) [Cantidad],
        'BLANCOS' CALIDAD
    from CBases c
    inner join v_Bases v on v.CODIGO=c.basecodi
    where v.[NOMBRE COMPLETO] like '%blanco%' and
    c.colorcodi=@COLORCODI
    union
    select
        SUM(cbs),
        'ACCENT'
    from CBases c
    inner join v_Bases v on v.CODIGO=c.basecodi
    where v.[NOMBRE COMPLETO] like '%Acce%' 
    and c.colorcodi=@COLORCODI
)
SET @TOTALBASES=
(
SELECT sum(CANTIDAD) TOTAL 
FROM Consolidado_B
)

Answer 1

Use can just use a SELECT. But this would be simpler as:

select @TOTALBASES = SUM(cbs) 
from CBases c join
     v_Bases v
     on v.CODIGO=c.basecodi
where c.colorcodi = @COLORCODI or
      (v.[NOMBRE COMPLETO] like '%blanco%'  or v.[NOMBRE COMPLETO] like '%Acce%') ;

This is not 100% equivalent if [NOMBRE COMPLETO] can match both conditions -- and you actually want those counted twice. But, it is much, much more efficient. If that is the case, the query can be tweaked to handle this -- without all the complication of your query.

Answer 2

Since you didn't mention it, i'll guess the error message is : Subquery returned more than 1 value. or Consolidado_B isn't defined.

The CTE need to be with the select statement, not separate with the SET statement.

Try this :

WITH Consolidado_B (CANTIDAD,CALIDAD) AS(
    select 
        SUM(cbs) [Cantidad],
        'BLANCOS' CALIDAD
    from CBases c
    inner join v_Bases v on v.CODIGO=c.basecodi
    where v.[NOMBRE COMPLETO] like '%blanco%' and
    c.colorcodi=@COLORCODI
    union
    select
        SUM(cbs),
        'ACCENT'
    from CBases c
    inner join v_Bases v on v.CODIGO=c.basecodi
    where v.[NOMBRE COMPLETO] like '%Acce%' 
    and c.colorcodi=@COLORCODI
)
SELECT @TOTALBASES= sum(CANTIDAD) 
FROM Consolidado_B