You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
46 lines
2.4 KiB
46 lines
2.4 KiB
USE [Vertragsverwaltung_20160404]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_get_Lizenzstruktur_up] Script Date: 02.12.2016 09:08:53 ******/
|
|
DROP PROCEDURE [dbo].[sp_get_Lizenzstruktur_up]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_get_Lizenzstruktur_up] Script Date: 02.12.2016 09:08:55 ******/
|
|
SET ANSI_NULLS OFF
|
|
GO
|
|
SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
CREATE proc [dbo].[sp_get_Lizenzstruktur_up]
|
|
@Root int,
|
|
@mitarbeiternr int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
declare @xx varchar(255)
|
|
|
|
declare @Lizid int, @bezeichnung varchar(50), @nr int, @parent int, @seclevel int, @aktiv bit,@st int
|
|
|
|
select @bezeichnung=bezeichnung, @nr=Lizenznr, @parent=parentid, @seclevel=securitylevelnr, @aktiv=aktiv,@st=strukturelement from Lizenz
|
|
where Lizenznr=@root
|
|
|
|
insert into #tmpd (Lizenznr, bezeichnung, parentid, securitylevelnr, aktiv,strukturelement) values (@nr, @bezeichnung, @parent,@seclevel,@aktiv,@st)
|
|
|
|
set @Lizid=(select parentid from lizenz where lizenznr=@nr)
|
|
|
|
while @Lizid is not null begin
|
|
exec dbo.sp_get_Lizenzstruktur_up @Lizid, 1
|
|
set @Lizid=(select parentid from dbo.Lizenz
|
|
where Lizenznr = @nr and parentid>@Lizid)
|
|
end
|
|
end
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|