USE [Vertragsverwaltung_20160404]
GO
/****** Object:  StoredProcedure [dbo].[sp_vertragselement_get_childs]    Script Date: 02.12.2016 09:08:53 ******/
DROP PROCEDURE [dbo].[sp_vertragselement_get_childs]
GO
/****** Object:  StoredProcedure [dbo].[sp_vertragselement_get_childs]    Script Date: 02.12.2016 09:08:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO




CREATE proc  [dbo].[sp_vertragselement_get_childs] 
@vertragselementnr int,
@mitarbeiternr int,
@partnernr int
as
set nocount on 

CREATE TABLE #tmpd(
	[vertragselementnr] [int]  ,
	[bezeichnung] [varchar] (255) null,
	[parentid] [int] null,
	[Vertragstypnr] [int],
	[aktiv] [bit] null,
    [vertragselementnrursprung] [int] null,
    [basevertragstypnr] [int] null,
    [key] [int] IDENTITY(1,1) NOT NULL,
    [level] int null,
    [loopcnt] int null,
	
) ON [PRIMARY]

if @partnernr<>0 begin
   select vertragselementnr,bezeichnung from Vertragselement where VertragspartnerNr=@partnernr and Aktiv=1
   return
end   

declare @vnr int
declare @vtype varchar(255)
declare @tmpid int
declare @tmpid_base int
declare @aktiv bit
declare @tmp int
declare @loop int
declare @keyvalue int
declare @keyvalue1 int
declare @key int
declare @loopcnt int

set @tmpid=-100
set @loopcnt = 0


declare xy cursor for
   select Vertragselementnr, parentid, vertragstypnr from vertragselement
   where parentid=@vertragselementnr and aktiv = 1
         and vertragselementnr not in (select vertragselementnr from #tmpd)
   open xy
   fetch next from xy into @tmp, @tmpid, @vnr
   while @@fetch_status=0 begin
      execute dbo.sp_vertragsuebersicht_get_struktur_down @tmp,1, @vnr, @tmpid, @loopcnt
      set @loopcnt=@loopcnt+1
      fetch next from xy into @tmp, @tmpid, @vnr
   end
   close xy
   deallocate xy 

   select * from #tmpd
   drop table #tmpd
   

      




GO