------------------------------------------------------------
IF 0 = (SELECT count(*) from [sysobjects] where ([xtype] = 'U' or [xtype] = 'V') and [name] = 'ut_Ressourcen')
BEGIN
CREATE TABLE [dbo].[ut_Ressourcen]
(
   [RecId]  integer NOT NULL
  ,[MandId] integer DEFAULT 1
  ,[ResNr] char(4) DEFAULT ' '
  ,[ResTyp] char(1) DEFAULT ' '
  ,[IdentNr] char(4) DEFAULT ' '
  ,[Abteilung] char(4) DEFAULT ' '
  ,[KurzBezeichnung] char(42) DEFAULT ' '
  CONSTRAINT [Pk_Ressourcen]  PRIMARY KEY NONCLUSTERED ( [RecId] )
)
END
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Ressourcen_KeyDel')
  DROP TRIGGER [dbo].[tr_Ressourcen_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Ressourcen_KeyUpd')
  DROP TRIGGER [dbo].[tr_Ressourcen_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_Ressourcen_Keys')
  DROP TABLE [dbo].[ut_Ressourcen_Keys]
GO

IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'ResNr')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ADD [ResNr] char(4) NOT NULL DEFAULT ' '
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'ResTyp')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ADD [ResTyp] char(1) NOT NULL DEFAULT ' '
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'IdentNr')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ADD [IdentNr] char(4) NOT NULL DEFAULT ' '
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'Abteilung')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ADD [Abteilung] char(4) NOT NULL DEFAULT ' '
END
GO
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'KurzBezeichnung')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ADD [KurzBezeichnung] char(42) NOT NULL DEFAULT ' '
END
GO
IF 4 > (select length from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'ResNr')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ALTER COLUMN [ResNr] char(4)
END
GO
IF 1 > (select length from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'ResTyp')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ALTER COLUMN [ResTyp] char(1)
END
GO
IF 4 > (select length from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'IdentNr')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ALTER COLUMN [IdentNr] char(4)
END
GO
IF 4 > (select length from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'Abteilung')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ALTER COLUMN [Abteilung] char(4)
END
GO
IF 42 > (select length from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Ressourcen' and sc.[name] = 'KurzBezeichnung')
BEGIN
  ALTER TABLE [dbo].[ut_Ressourcen] ALTER COLUMN [KurzBezeichnung] char(42)
END
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'Ressourcen')
  DROP VIEW [dbo].[Ressourcen]
GO
CREATE VIEW [dbo].[Ressourcen] AS
  SELECT * FROM [dbo].[ut_Ressourcen]
GO

------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Add_Ressourcen')
  DROP PROCEDURE [dbo].[up_Add_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_Add_Ressourcen] AS
  DECLARE @NeueId integer
  SET @NeueId = (SELECT MAX(RecId) from [dbo].[ut_Ressourcen])
  IF @NeueId IS NULL SET @NeueId = 0
  SET @NeueId = @NeueId + 1
  INSERT INTO [dbo].[ut_Ressourcen] (RecId) VALUES( @NeueId )
  IF @@ERROR = 0
  BEGIN
    SELECT @NeueId AS RecId
    RETURN
  END
  SELECT 0 AS RecId
GO

------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd_Ressourcen')
  DROP PROCEDURE [dbo].[up_Upd_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_Upd_Ressourcen]
  @RI integer
 ,@Parm_ResNr char(4)
 ,@Parm_ResTyp char(1)
 ,@Parm_IdentNr char(4)
 ,@Parm_Abteilung char(4)
 ,@Parm_KurzBezeichnung char(42)
AS
--BEGIN TRANSACTION
UPDATE [dbo].[ut_Ressourcen] SET
     [ResNr] = RTRIM(@Parm_ResNr)
    ,[ResTyp] = RTRIM(@Parm_ResTyp)
    ,[IdentNr] = RTRIM(@Parm_IdentNr)
    ,[Abteilung] = RTRIM(@Parm_Abteilung)
    ,[KurzBezeichnung] = RTRIM(@Parm_KurzBezeichnung)
  WHERE RecId = @RI
--COMMIT TRANSACTION
GO

------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Ressourcen')
  DROP PROCEDURE [dbo].[up_Sel_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_Sel_Ressourcen]
  @RI integer
AS
SELECT
     [ResNr]
    ,[ResTyp]
    ,[IdentNr]
    ,[Abteilung]
    ,[KurzBezeichnung]
  FROM [dbo].[ut_Ressourcen]
  WHERE RecId = @RI
GO
------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Del_Ressourcen')
  DROP PROCEDURE [dbo].[up_Del_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_Del_Ressourcen]
  @RI integer
AS
  DELETE FROM [dbo].[ut_Ressourcen] WHERE RecId = @RI
GO
------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Reorg_Ressourcen')
  DROP PROCEDURE [dbo].[up_Reorg_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_Reorg_Ressourcen]
  @bDbcc integer,@bRecId integer
AS
  IF (@bRecId = 1)
  BEGIN
    DECLARE  C        CURSOR FOR SELECT RecId FROM [dbo].[ut_Ressourcen] ORDER BY RecId
    DECLARE  @CurrId  integer
    DECLARE  @NextId  integer
    --BEGIN TRANSACTION
    OPEN C
    SET @NextId = 1
    FETCH NEXT FROM C INTO @CurrId
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      IF @CurrId <> @NextId
      UPDATE [dbo].[ut_Ressourcen] SET RecId=@NextId WHERE CURRENT OF C
      SET @NextId = @NextId + 1
      FETCH NEXT FROM C INTO @CurrId
    END
    CLOSE C
    --COMMIT TRANSACTION
    DEALLOCATE C
  END

  IF (@bDbcc = 1)
  BEGIN
    DBCC DBREINDEX ( [ut_Ressourcen] )
  END

GO

------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_Ressourcen')
  DROP PROCEDURE [dbo].[up_Append_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_Append_Ressourcen]
  @Parm_ResNr char(4)
 ,@Parm_ResTyp char(1)
 ,@Parm_IdentNr char(4)
 ,@Parm_Abteilung char(4)
 ,@Parm_KurzBezeichnung char(42)
AS
  DECLARE @NeueId    integer
  SET     @NeueId = (SELECT MAX(RecId) FROM [dbo].[ut_Ressourcen])
  IF @NeueId IS NULL SET @NeueId = 0
  SET @NeueId = @NeueId + 1
  INSERT INTO [dbo].[ut_Ressourcen] (
    RecId
   ,[ResNr]
   ,[ResTyp]
   ,[IdentNr]
   ,[Abteilung]
   ,[KurzBezeichnung]
     ) VALUES (
    @NeueId
   ,RTRIM(@Parm_ResNr)
   ,RTRIM(@Parm_ResTyp)
   ,RTRIM(@Parm_IdentNr)
   ,RTRIM(@Parm_Abteilung)
   ,RTRIM(@Parm_KurzBezeichnung)
       )
  IF @@ERROR = 0
  BEGIN
    SELECT @NeueId AS RecId
    RETURN
  END
  SELECT 0 AS RecId
GO

------------------------------------------------------------
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Ressourcen')
  DROP PROCEDURE [dbo].[up_TrSel_Ressourcen]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Ressourcen]
AS
  SELECT   RecId,
      LTRIM(RTRIM([ResNr]))
      +LTRIM(RTRIM([ResTyp]))
      +LTRIM(RTRIM([IdentNr]))
      +LTRIM(RTRIM([Abteilung]))
      +LTRIM(RTRIM([KurzBezeichnung]))
    AS Data
  FROM     [dbo].[ut_Ressourcen]
  WHERE    RecId > 0
  ORDER BY RecId
GO

IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Ressourcen'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_Key0')
CREATE INDEX [Ik_Key0] ON [dbo].[ut_Ressourcen]
(ResNr,RecId)
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_First_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_Last_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_Prior_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_Next_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_Prior50_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_Next50_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_FindL_Ressourcen_Key0]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Ressourcen_Key0')
  DROP PROCEDURE [dbo].[up_FindF_Ressourcen_Key0]
GO

------------------------------------------------------------
/* ersten Datensatz aus Ressourcen in Folge Key0 ermittln */
CREATE PROCEDURE [dbo].[up_First_Ressourcen_Key0] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY ResNr,RecId
GO

------------------------------------------------------------
/* letzten Datensatz aus Ressourcen in Folge Key0 ermittln */
CREATE PROCEDURE [dbo].[up_Last_Ressourcen_Key0] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY ResNr DESC,RecId DESC
GO

------------------------------------------------------------
/* naechsten Datensatz in Folge ORDER BY ResNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_Ressourcen_Key0] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldResNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT ResNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldResNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResNr = @oldResNr AND RecId > @oldRecId
                   ORDER BY ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResNr > @oldResNr
                   ORDER BY ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY ResNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_Ressourcen_Key0] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldResNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT ResNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldResNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResNr = @oldResNr AND RecId < @oldRecId
                   ORDER BY ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResNr < @oldResNr
                   ORDER BY ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* ersten Datensatz in Folge ORDER BY ResNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_Ressourcen_Key0] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(ResNr,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY ResNr,RecId
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResNr = SUBSTRING(@xKey,1,4)
      ORDER BY ResNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

------------------------------------------------------------
/* letzten Datensatz in Folge ORDER BY ResNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_Ressourcen_Key0] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(ResNr,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY ResNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResNr = SUBSTRING(@xKey,1,4)
      ORDER BY ResNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Ressourcen'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_Key1')
CREATE INDEX [Ik_Key1] ON [dbo].[ut_Ressourcen]
(IdentNr,RecId)
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_First_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_Last_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_Prior_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_Next_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_Prior50_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_Next50_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_FindL_Ressourcen_Key1]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Ressourcen_Key1')
  DROP PROCEDURE [dbo].[up_FindF_Ressourcen_Key1]
GO

------------------------------------------------------------
/* ersten Datensatz aus Ressourcen in Folge Key1 ermittln */
CREATE PROCEDURE [dbo].[up_First_Ressourcen_Key1] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY IdentNr,RecId
GO

------------------------------------------------------------
/* letzten Datensatz aus Ressourcen in Folge Key1 ermittln */
CREATE PROCEDURE [dbo].[up_Last_Ressourcen_Key1] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY IdentNr DESC,RecId DESC
GO

------------------------------------------------------------
/* naechsten Datensatz in Folge ORDER BY IdentNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_Ressourcen_Key1] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldIdentNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT IdentNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldIdentNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE IdentNr = @oldIdentNr AND RecId > @oldRecId
                   ORDER BY IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE IdentNr > @oldIdentNr
                   ORDER BY IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY IdentNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_Ressourcen_Key1] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldIdentNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT IdentNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldIdentNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE IdentNr = @oldIdentNr AND RecId < @oldRecId
                   ORDER BY IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE IdentNr < @oldIdentNr
                   ORDER BY IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* ersten Datensatz in Folge ORDER BY IdentNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_Ressourcen_Key1] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(IdentNr,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY IdentNr,RecId
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE IdentNr = SUBSTRING(@xKey,1,4)
      ORDER BY IdentNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

------------------------------------------------------------
/* letzten Datensatz in Folge ORDER BY IdentNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_Ressourcen_Key1] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(IdentNr,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY IdentNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE IdentNr = SUBSTRING(@xKey,1,4)
      ORDER BY IdentNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Ressourcen'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_Key2')
CREATE INDEX [Ik_Key2] ON [dbo].[ut_Ressourcen]
(ResTyp,ResNr,RecId)
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_First_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_Last_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_Prior_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_Next_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_Prior50_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_Next50_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_FindL_Ressourcen_Key2]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Ressourcen_Key2')
  DROP PROCEDURE [dbo].[up_FindF_Ressourcen_Key2]
GO

------------------------------------------------------------
/* ersten Datensatz aus Ressourcen in Folge Key2 ermittln */
CREATE PROCEDURE [dbo].[up_First_Ressourcen_Key2] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY ResTyp,ResNr,RecId
GO

------------------------------------------------------------
/* letzten Datensatz aus Ressourcen in Folge Key2 ermittln */
CREATE PROCEDURE [dbo].[up_Last_Ressourcen_Key2] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
GO

------------------------------------------------------------
/* naechsten Datensatz in Folge ORDER BY ResTyp,ResNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_Ressourcen_Key2] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldResTyp char(1)
  DECLARE @oldResNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT ResTyp,ResNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldResTyp,@oldResNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND ResNr = @oldResNr AND RecId > @oldRecId
                   ORDER BY ResTyp,ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND ResNr > @oldResNr
                   ORDER BY ResTyp,ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp > @oldResTyp
                   ORDER BY ResTyp,ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY ResTyp DESC,ResNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_Ressourcen_Key2] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldResTyp char(1)
  DECLARE @oldResNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT ResTyp,ResNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldResTyp,@oldResNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND ResNr = @oldResNr AND RecId < @oldRecId
                   ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND ResNr < @oldResNr
                   ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp < @oldResTyp
                   ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* ersten Datensatz in Folge ORDER BY ResTyp,ResNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_Ressourcen_Key2] @xKey char(128),@xLen integer AS

  IF @xLen = 1
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1)
      ORDER BY ResTyp,ResNr,RecId
    RETURN
  END

  IF @xLen < 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND SUBSTRING(ResNr,1,@xLen-1) = SUBSTRING(@xKey,2,@xLen-1)
      ORDER BY ResTyp,ResNr,RecId
    RETURN
  END

  IF @xLen = 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND ResNr = SUBSTRING(@xKey,2,4)
      ORDER BY ResTyp,ResNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

------------------------------------------------------------
/* letzten Datensatz in Folge ORDER BY ResTyp DESC,ResNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_Ressourcen_Key2] @xKey char(128),@xLen integer AS

  IF @xLen = 1
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1)
      ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
    RETURN
  END

  IF @xLen < 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND SUBSTRING(ResNr,1,@xLen-1) = SUBSTRING(@xKey,2,@xLen-1)
      ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND ResNr = SUBSTRING(@xKey,2,4)
      ORDER BY ResTyp DESC,ResNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Ressourcen'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_Key3')
CREATE INDEX [Ik_Key3] ON [dbo].[ut_Ressourcen]
(ResTyp,IdentNr,RecId)
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_First_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_Last_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_Prior_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_Next_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_Prior50_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_Next50_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_FindL_Ressourcen_Key3]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Ressourcen_Key3')
  DROP PROCEDURE [dbo].[up_FindF_Ressourcen_Key3]
GO

------------------------------------------------------------
/* ersten Datensatz aus Ressourcen in Folge Key3 ermittln */
CREATE PROCEDURE [dbo].[up_First_Ressourcen_Key3] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY ResTyp,IdentNr,RecId
GO

------------------------------------------------------------
/* letzten Datensatz aus Ressourcen in Folge Key3 ermittln */
CREATE PROCEDURE [dbo].[up_Last_Ressourcen_Key3] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
GO

------------------------------------------------------------
/* naechsten Datensatz in Folge ORDER BY ResTyp,IdentNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_Ressourcen_Key3] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldResTyp char(1)
  DECLARE @oldIdentNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT ResTyp,IdentNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldResTyp,@oldIdentNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND IdentNr = @oldIdentNr AND RecId > @oldRecId
                   ORDER BY ResTyp,IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND IdentNr > @oldIdentNr
                   ORDER BY ResTyp,IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp > @oldResTyp
                   ORDER BY ResTyp,IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_Ressourcen_Key3] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldResTyp char(1)
  DECLARE @oldIdentNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT ResTyp,IdentNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldResTyp,@oldIdentNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND IdentNr = @oldIdentNr AND RecId < @oldRecId
                   ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp = @oldResTyp AND IdentNr < @oldIdentNr
                   ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE ResTyp < @oldResTyp
                   ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* ersten Datensatz in Folge ORDER BY ResTyp,IdentNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_Ressourcen_Key3] @xKey char(128),@xLen integer AS

  IF @xLen = 1
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1)
      ORDER BY ResTyp,IdentNr,RecId
    RETURN
  END

  IF @xLen < 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND SUBSTRING(IdentNr,1,@xLen-1) = SUBSTRING(@xKey,2,@xLen-1)
      ORDER BY ResTyp,IdentNr,RecId
    RETURN
  END

  IF @xLen = 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND IdentNr = SUBSTRING(@xKey,2,4)
      ORDER BY ResTyp,IdentNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

------------------------------------------------------------
/* letzten Datensatz in Folge ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_Ressourcen_Key3] @xKey char(128),@xLen integer AS

  IF @xLen = 1
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1)
      ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  IF @xLen < 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND SUBSTRING(IdentNr,1,@xLen-1) = SUBSTRING(@xKey,2,@xLen-1)
      ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 5
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE ResTyp = SUBSTRING(@xKey,1,1) AND IdentNr = SUBSTRING(@xKey,2,4)
      ORDER BY ResTyp DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Ressourcen'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_Key4')
CREATE INDEX [Ik_Key4] ON [dbo].[ut_Ressourcen]
(Abteilung,ResNr,RecId)
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_First_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_Last_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_Prior_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_Next_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_Prior50_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_Next50_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_FindL_Ressourcen_Key4]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Ressourcen_Key4')
  DROP PROCEDURE [dbo].[up_FindF_Ressourcen_Key4]
GO

------------------------------------------------------------
/* ersten Datensatz aus Ressourcen in Folge Key4 ermittln */
CREATE PROCEDURE [dbo].[up_First_Ressourcen_Key4] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY Abteilung,ResNr,RecId
GO

------------------------------------------------------------
/* letzten Datensatz aus Ressourcen in Folge Key4 ermittln */
CREATE PROCEDURE [dbo].[up_Last_Ressourcen_Key4] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
GO

------------------------------------------------------------
/* naechsten Datensatz in Folge ORDER BY Abteilung,ResNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_Ressourcen_Key4] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldAbteilung char(4)
  DECLARE @oldResNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT Abteilung,ResNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldAbteilung,@oldResNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND ResNr = @oldResNr AND RecId > @oldRecId
                   ORDER BY Abteilung,ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND ResNr > @oldResNr
                   ORDER BY Abteilung,ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung > @oldAbteilung
                   ORDER BY Abteilung,ResNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY Abteilung DESC,ResNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_Ressourcen_Key4] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldAbteilung char(4)
  DECLARE @oldResNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT Abteilung,ResNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldAbteilung,@oldResNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND ResNr = @oldResNr AND RecId < @oldRecId
                   ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND ResNr < @oldResNr
                   ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung < @oldAbteilung
                   ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* ersten Datensatz in Folge ORDER BY Abteilung,ResNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_Ressourcen_Key4] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(Abteilung,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY Abteilung,ResNr,RecId
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4)
      ORDER BY Abteilung,ResNr,RecId
    RETURN
  END

  IF @xLen < 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND SUBSTRING(ResNr,1,@xLen-4) = SUBSTRING(@xKey,5,@xLen-4)
      ORDER BY Abteilung,ResNr,RecId
    RETURN
  END

  IF @xLen = 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND ResNr = SUBSTRING(@xKey,5,4)
      ORDER BY Abteilung,ResNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

------------------------------------------------------------
/* letzten Datensatz in Folge ORDER BY Abteilung DESC,ResNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_Ressourcen_Key4] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(Abteilung,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4)
      ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
    RETURN
  END

  IF @xLen < 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND SUBSTRING(ResNr,1,@xLen-4) = SUBSTRING(@xKey,5,@xLen-4)
      ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND ResNr = SUBSTRING(@xKey,5,4)
      ORDER BY Abteilung DESC,ResNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

IF 0 = (select count(*)
   from       sysindexes si
   inner join sysobjects so on
   so.[xtype]    = 'U'
   and so.[name] = 'ut_Ressourcen'
   and so.[id]   = si.[id]
   and si.[name] = 'Ik_Key5')
CREATE INDEX [Ik_Key5] ON [dbo].[ut_Ressourcen]
(Abteilung,IdentNr,RecId)
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_First_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_Last_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_Prior_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_Next_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_Prior50_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_Next50_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_FindL_Ressourcen_Key5]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Ressourcen_Key5')
  DROP PROCEDURE [dbo].[up_FindF_Ressourcen_Key5]
GO

------------------------------------------------------------
/* ersten Datensatz aus Ressourcen in Folge Key5 ermittln */
CREATE PROCEDURE [dbo].[up_First_Ressourcen_Key5] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY Abteilung,IdentNr,RecId
GO

------------------------------------------------------------
/* letzten Datensatz aus Ressourcen in Folge Key5 ermittln */
CREATE PROCEDURE [dbo].[up_Last_Ressourcen_Key5] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen] ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
GO

------------------------------------------------------------
/* naechsten Datensatz in Folge ORDER BY Abteilung,IdentNr,RecId ermitteln */
CREATE PROCEDURE [dbo].[up_Next_Ressourcen_Key5] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldAbteilung char(4)
  DECLARE @oldIdentNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT Abteilung,IdentNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldAbteilung,@oldIdentNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND IdentNr = @oldIdentNr AND RecId > @oldRecId
                   ORDER BY Abteilung,IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND IdentNr > @oldIdentNr
                   ORDER BY Abteilung,IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung > @oldAbteilung
                   ORDER BY Abteilung,IdentNr,RecId
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* vorherigen Datensatz in Folge ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC ermitteln */
CREATE PROCEDURE [dbo].[up_Prior_Ressourcen_Key5] @oldRecId integer AS
  DECLARE @newRecId integer
  DECLARE @oldAbteilung char(4)
  DECLARE @oldIdentNr char(4)

  /* alte Daten ermitteln */
  DECLARE curTmp CURSOR FOR SELECT Abteilung,IdentNr FROM [dbo].[ut_Ressourcen] WHERE RecId=@oldRecId
  OPEN    curTmp
  FETCH NEXT FROM curTmp INTO @oldAbteilung,@oldIdentNr
  IF (@@FETCH_STATUS <> 0)
  BEGIN
    CLOSE      curTmp
    DEALLOCATE curTmp
    SET @newRecId = 0
    SELECT @newRecId AS RecId
    RETURN
  END
  CLOSE      curTmp
  DEALLOCATE curTmp

  /* Folgesaetze aufsuchen */
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND IdentNr = @oldIdentNr AND RecId < @oldRecId
                   ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung = @oldAbteilung AND IdentNr < @oldIdentNr
                   ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END
  SET @newRecId = (SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
                   WHERE Abteilung < @oldAbteilung
                   ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
                  )
  IF (NOT (@newRecId IS NULL))
  BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  /* keine weiteren Datensaetze */
  SET @newRecId = 0
  SELECT @newRecId AS RecId
GO

------------------------------------------------------------
/* ersten Datensatz in Folge ORDER BY Abteilung,IdentNr,RecId suchen */
CREATE PROCEDURE [dbo].[up_FindF_Ressourcen_Key5] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(Abteilung,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY Abteilung,IdentNr,RecId
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4)
      ORDER BY Abteilung,IdentNr,RecId
    RETURN
  END

  IF @xLen < 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND SUBSTRING(IdentNr,1,@xLen-4) = SUBSTRING(@xKey,5,@xLen-4)
      ORDER BY Abteilung,IdentNr,RecId
    RETURN
  END

  IF @xLen = 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND IdentNr = SUBSTRING(@xKey,5,4)
      ORDER BY Abteilung,IdentNr,RecId
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

------------------------------------------------------------
/* letzten Datensatz in Folge ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC suchen */
CREATE PROCEDURE [dbo].[up_FindL_Ressourcen_Key5] @xKey char(128),@xLen integer AS

  IF @xLen < 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE SUBSTRING(Abteilung,1,@xLen) = SUBSTRING(@xKey,1,@xLen)
      ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 4
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4)
      ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  IF @xLen < 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND SUBSTRING(IdentNr,1,@xLen-4) = SUBSTRING(@xKey,5,@xLen-4)
      ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  IF @xLen = 8
  BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Ressourcen]
      WHERE Abteilung = SUBSTRING(@xKey,1,4) AND IdentNr = SUBSTRING(@xKey,5,4)
      ORDER BY Abteilung DESC,IdentNr DESC,RecId DESC
    RETURN
  END

  SELECT CONVERT(integer,0) AS RecId
GO

