CODEWITHSUNDEEP
sqlsql-servert-sql
Pinx0
Pinx0

Reputation: 1258

SQL Select statement as variable

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?

Upvotes: 0

Views: 93

Answers (2)

G B
G B

Reputation: 1462

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)

Upvotes: 1

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

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

Upvotes: 0

Related Questions