salut, are cineva inspiratie si timp sa ma ajute sa optimizez si eu urmatoarea procedura.....sau o idee ceva.....multumesc anticipat
am procedura:
CREATE PROCEDURE dbo.cg_GetCentralizatorRamasite_PF_PJ_ListaPeCreanta
@dataCurenta smalldatetime,
@idprim uniqueidentifier,
@filtru_tip_persoana tinyint
AS
IF @filtru_tip_persoana = 1
begin
select distinct
dbo.GetGrupareByIDTipMatricola(dbf.id_tipmatr,dbf.id_matr) as grup,
dbf.an,
coalesce(
(select sum(suma)
from gotax.dbo.debite_pf p
inner join gotax.dbo.roluri_fiz r
on r.id = p.id_rol
where
p.id_tipdebit = 2
and p.an = dbf.an
and dbo.GetGrupareByIDTipMatricola(dbf.id_tipmatr,dbf.id_matr)
=
dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
and r.id_prim = @idprim
)
,0)
as ramasite,
coalesce(
(select sum(suma)
from gotax.dbo.debite_pf p
inner join gotax.dbo.roluri_fiz r
on r.id = p.id_rol
where
(p.id_tipdebit = 5 or p.id_tipdebit = 8)
and p.an = dbf.an
and dbo.GetGrupareByIDTipMatricola(dbf.id_tipmatr,dbf.id_matr)
=
dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
and r.id_prim = @idprim
)
,0)
as accesorii
,dbf.id_tipmatr
from gotax.dbo.roluri_fiz r
inner join gotax.dbo.debite_pf dbf
on r.id = dbf.id_rol
where r.id_prim = @idprim and
dbf.id_tipdebit in (2,5,8)
order by dbf.id_tipmatr
end
IF @filtru_tip_persoana = 2
begin
select distinct
dbo.GetGrupareByIDTipMatricola(dbj.id_tipmatr,dbj.id_matr) as grup,
dbj.an,
coalesce(
(select sum(suma)
from gotax.dbo.debite_pj p
inner join gotax.dbo.roluri_jur r
on r.id = p.id_rol
where
p.id_tipdebit = 2
and p.an = dbj.an
and dbo.GetGrupareByIDTipMatricola(dbj.id_tipmatr,dbj.id_matr)
=
dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
and r.id_prim = @idprim
)
,0)
as ramasite,
coalesce(
(select sum(suma)
from gotax.dbo.debite_pj p
inner join gotax.dbo.roluri_jur r
on r.id = p.id_rol
where
(p.id_tipdebit = 5 or p.id_tipdebit = 8)
and p.an = dbj.an
and dbo.GetGrupareByIDTipMatricola(dbj.id_tipmatr,dbj.id_matr)
=
dbo.GetGrupareByIDTipMatricola(p.id_tipmatr,p.id_matr)
and r.id_prim = @idprim
)
,0)
as accesorii
,dbj.id_tipmatr
from gotax.dbo.roluri_jur r
inner join gotax.dbo.debite_pj dbj
on r.id = dbj.id_rol
where r.id_prim = @idprim and
dbj.id_tipdebit in (2,5,8)
order by dbj.id_tipmatr
end
GO
si functia care e folosita in procedura:
CREATE FUNCTION GetGrupareByIDTipMatricola (@id_tipmatr tinyint,@id_matr uniqueidentifier)
RETURNS varchar(100) AS
BEGIN
declare @aux varchar(100)
if @id_tipmatr = 1 begin set @Aux = (select label from gotax.dbo.tip_matricole where id = 1) end
if @id_tipmatr = 2 begin set @Aux =(select label from gotax.dbo.tip_matricole where id = 2) end
if @id_tipmatr = 3 begin set @Aux = (select label from gotax.dbo.tip_matricole where id = 3) end
if @id_tipmatr = 4 begin set @Aux =
(select denumire from gotax.dbo.tip_alte_taxe where id =
coalesce(
(select id_tipalte from gotax.dbo.matricole_alte_pf where id = @id_matr),
(select id_tipalte from gotax.dbo.matricole_alte_pj where id = @id_matr)
)
) end
return @aux
END
alex.