SQL Select statement as variable

Question

So I need to use the data returned from one long select, several times in another one.

I could use subqueries, but I dont' want to copy the code several times because I might need to modify it.

So I'm trying to do something like declare a temporary view or something that achieves the same purpose:

DECLARE @myView as view
SET @myView = (select ob.IdOrden, obs.idarticulo, a.descrip, obs.p1,cc.descrip, SUM(isnull(t.Minutos,0)) Minutos
from Ordenes_Bonos ob
left join (select SUM(DATEDIFF(MINUTE ,FechaIni,FechaFin)) Minutos,IdOrden, IdBono, IdEmpleado 
 from dbo.Pers_Tiempos_Bonos 
 group by IdOrden, IdBono, IdEmpleado) t on t.IdOrden = ob.IdOrden and t.IdBono = ob.IdBono
left join Ordenes_bonos_Salidas obs on obs.idorden = ob.idorden and obs.idbono = ob.IdBono
inner join Colores_cartas cc on cc.idcolor = obs.p1
inner join articulos a on a.idarticulo = obs.idarticulo
where ob.Descrip like 'prod%' and t.minutos > 5 and t.minutos < 1500
GROUP BY ob.IdOrden, obs.idarticulo, a.descrip, obs.p1, cc.descrip)

But this doesn't work and it seems it's not allowed. I don't have permissions to make a permanent view so that's not an option.

I want that so later I could do my other query as:

declare @pp float
set @pp = .4
select @pp as factor, avg(cast(Minutos as float)) as TrimmedMeanP from @TestScores a 
where (select count(*) from @myView aa where aa.Minutos <= a.Minutos) >= (select @pp*count(*) from @myView) 
and (select count(*) from @myView bb where bb.Minutos >= a.Minutos) >= (select @pp*count(*) from @myView)

Without having to repeat the first one every time.

Any ideas?

• Ivan Sivak: Could you use Table Valued functions? This would allow you to change result based on input arguments.
• Pinx0: No, I don't have CREATE permissions.
• Ivan Sivak: Maybe Common Table Expressions would help you

Answer 1

you do this with a CTE (Common Table Expression) it is basically a temporary table that is scoped to a single query.

declare @pp float
set @pp = .4
;with CTE as 
 (select 
 ob.IdOrden, 
 obs.idarticulo, 
 a.descrip, 
 obs.p1,cc.descrip, 
 SUM(isnull(t.Minutos,0)) Minutos
 from 
 Ordenes_Bonos ob
 left join 
 (select SUM(DATEDIFF(MINUTE ,FechaIni,FechaFin)) Minutos,IdOrden, IdBono, IdEmpleado 
 from dbo.Pers_Tiempos_Bonos 
 group by IdOrden, IdBono, IdEmpleado) t 
 on t.IdOrden = ob.IdOrden and t.IdBono = ob.IdBono
 left join Ordenes_bonos_Salidas obs 
 on obs.idorden = ob.idorden 
 and obs.idbono = ob.IdBono
 inner join Colores_cartas cc 
 on cc.idcolor = obs.p1
 inner join articulos a 
 on a.idarticulo = obs.idarticulo
where 
 ob.Descrip like 'prod%'
 and t.minutos > 5 
 and t.minutos < 1500
GROUP BY
 ob.IdOrden, 
 obs.idarticulo, 
 a.descrip, 
 obs.p1, 
 cc.descrip)
select
 @pp as factor, 
 avg(cast(Minutos as float)) as TrimmedMeanP 
from @TestScores a 
where 
 (select count(*) from CTE aa where aa.Minutos <= a.Minutos) >= (select @pp*count(*) from CTE) 
 and (select count(*) from CTE bb where bb.Minutos >= a.Minutos) >= (select @pp*count(*) from CTE)

Answer 2

First of all declare dynamic @sql and set your query for that. Later declare variable @result as table and pass @sql for new created table. You could you something like that.

Documentation about sp_executesql

DECLARE @sql AS NVARCHAR(MAX)
SET @sql = (select ob.IdOrden, obs.idarticulo, a.descrip, obs.p1,cc.descrip, SUM(isnull(t.Minutos,0)) Minutos
from Ordenes_Bonos ob
left join (select SUM(DATEDIFF(MINUTE ,FechaIni,FechaFin)) Minutos,IdOrden, IdBono, IdEmpleado 
 from dbo.Pers_Tiempos_Bonos 
 group by IdOrden, IdBono, IdEmpleado) t on t.IdOrden = ob.IdOrden and t.IdBono = ob.IdBono
left join Ordenes_bonos_Salidas obs on obs.idorden = ob.idorden and obs.idbono = ob.IdBono
inner join Colores_cartas cc on cc.idcolor = obs.p1
inner join articulos a on a.idarticulo = obs.idarticulo
where ob.Descrip like 'prod%' and t.minutos > 5 and t.minutos < 1500
GROUP BY ob.IdOrden, obs.idarticulo, a.descrip, obs.p1, cc.descrip)
DECLARE @results AS TABLE 
 ( 
 --table structure
 ) 
INSERT INTO @results EXECUTE sp_executesql @sql

• HoneyBadger: You might want to add a quote or two.