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.
40 lines
2.3 KiB
40 lines
2.3 KiB
USE [Vertragsverwaltung_20160404]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_get_applstruktur_down] Script Date: 02.12.2016 09:08:53 ******/
|
|
DROP PROCEDURE [dbo].[sp_get_applstruktur_down]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_get_applstruktur_down] Script Date: 02.12.2016 09:08:55 ******/
|
|
SET ANSI_NULLS OFF
|
|
GO
|
|
SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
|
|
|
|
|
|
CREATE proc [dbo].[sp_get_applstruktur_down]
|
|
@Root int,
|
|
@imandant int,
|
|
@vnr int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON
|
|
declare @anr int, @bez varchar(50), @nr int, @parent int, @seclevel int, @aktiv bit
|
|
select @bez=bezeichnung, @nr=applikationnr, @parent=parentid, @seclevel=securitylevelnr, @aktiv=aktiv
|
|
from applikation
|
|
where applikationnr=@root
|
|
if @parent > 0 begin
|
|
insert into #tmpd (applikationsnr, bezeichnung, parentid,aktiv) values
|
|
(@nr, @bez, @parent,@aktiv)
|
|
end
|
|
set @anr=(select min (applikationnr) from applikation where parentid=@root)
|
|
while @anr is not null begin
|
|
exec dbo.sp_get_applstruktur_down @anr, 1, -1
|
|
set @anr=(select min (applikationnr) from dbo.applikation where parentid=@root and applikationnr>@anr)
|
|
end
|
|
end
|
|
|
|
|
|
|
|
|
|
GO
|