-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2026, All rights reserved by Steffen Anders, Elsteraue - Germany
-- Visit also https://www.it-crafts.de.
--

IF 0 = (SELECT count(*) from [sysobjects] where ([xtype] = 'U' or [xtype] = 'V') and [name] = 'ut_Bankleitzahlen')
BEGIN
CREATE TABLE [dbo].[ut_Bankleitzahlen]
(
  [RecId]  integer NOT NULL
 ,[BLZ] char(8) DEFAULT ''
 ,[Bankname] char(30) DEFAULT ''
 ,[SWIFT_BIC] char(11) DEFAULT ''
 ,[Bankbez] varchar(60) DEFAULT ''
 ,[Plz] char(5) DEFAULT ''
 ,[Ort] varchar(35) DEFAULT ''
 ,[KzPz] char(2) DEFAULT ''
 ,[Pan] char(5) DEFAULT ''
  CONSTRAINT [Pk_Bankleitzahlen]  PRIMARY KEY NONCLUSTERED ( [RecId] )
)
END
GO

-- drop obsolete triggers and the triggered table with keyvalues
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Bankleitzahlen_KeyDel')
  DROP TRIGGER [dbo].[tr_Bankleitzahlen_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Bankleitzahlen_KeyUpd')
  DROP TRIGGER [dbo].[tr_Bankleitzahlen_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_Bankleitzahlen_Keys')
  DROP TABLE [dbo].[ut_Bankleitzahlen_Keys]
GO

-- add new fields
IF 0 = (select count(*) from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'BLZ')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [BLZ] char(8) 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_Bankleitzahlen' and sc.[name] = 'Bankname')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [Bankname] char(30) 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_Bankleitzahlen' and sc.[name] = 'SWIFT_BIC')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [SWIFT_BIC] char(11) 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_Bankleitzahlen' and sc.[name] = 'Bankbez')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [Bankbez] varchar(60) 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_Bankleitzahlen' and sc.[name] = 'Plz')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [Plz] char(5) 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_Bankleitzahlen' and sc.[name] = 'Ort')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [Ort] varchar(35) 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_Bankleitzahlen' and sc.[name] = 'KzPz')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [KzPz] char(2) 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_Bankleitzahlen' and sc.[name] = 'Pan')
BEGIN
  ALTER TABLE [dbo].[ut_Bankleitzahlen] ADD [Pan] char(5) NOT NULL DEFAULT ''
END
GO

-- check lengths of text fields
IF (8 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'BLZ'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [BLZ] char(8)
END
GO
IF (8 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'BLZ'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [BLZ] varchar(8)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankname'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankname'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Bankname] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankname'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankname'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Bankname] varchar(30)
END
GO
IF (11 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'SWIFT_BIC'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'SWIFT_BIC'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [SWIFT_BIC] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'SWIFT_BIC'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'SWIFT_BIC'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [SWIFT_BIC] varchar(11)
END
GO
IF (60 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankbez'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankbez'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Bankbez] char(60)
END
GO
IF (60 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankbez'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Bankbez'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Bankbez] varchar(60)
END
GO
IF (5 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Plz'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Plz'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Plz] char(5)
END
GO
IF (5 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Plz'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Plz'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Plz] varchar(5)
END
GO
IF (35 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Ort'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Ort'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Ort] char(35)
END
GO
IF (35 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Ort'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Ort'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Ort] varchar(35)
END
GO
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'KzPz'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'KzPz'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [KzPz] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'KzPz'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'KzPz'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [KzPz] varchar(2)
END
GO
IF (5 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Pan'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Pan'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Pan] char(5)
END
GO
IF (5 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Pan'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Bankleitzahlen' and sc.[name] = 'Pan'))
BEGIN
   ALTER TABLE [dbo].[ut_Bankleitzahlen] ALTER COLUMN [Pan] varchar(5)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'Bankleitzahlen')
  DROP VIEW [dbo].[Bankleitzahlen]
GO
CREATE VIEW [dbo].[Bankleitzahlen] AS
  SELECT [RecId]
 ,[BLZ]
 ,[Bankname]
 ,[SWIFT_BIC]
 ,[Bankbez]
 ,[Plz]
 ,[Ort]
 ,[KzPz]
 ,[Pan]
  FROM [dbo].[ut_Bankleitzahlen]
GO


-- create function to get the next RecId
IF OBJECT_ID (N'itcfunc_NextRecId_Bankleitzahlen', N'FN') IS NOT NULL
  DROP FUNCTION dbo.itcfunc_NextRecId_Bankleitzahlen
GO
CREATE FUNCTION dbo.itcfunc_NextRecId_Bankleitzahlen() RETURNS integer
AS
BEGIN
  RETURN isnull((SELECT MAX(RecId) FROM [dbo].[ut_Bankleitzahlen] WITH (READUNCOMMITTED)),0) + 1
END
GO

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Sel_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Sel_Bankleitzahlen]  @mRI integer
AS
  SELECT
    [BLZ]
   ,[Bankname]
   ,[SWIFT_BIC]
   ,[Bankbez]
   ,[Plz]
   ,[Ort]
   ,[KzPz]
   ,[Pan]
  FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @mRI
GO

-- select with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel2_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Sel2_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Sel2_Bankleitzahlen]  @Param_RecId varchar(20)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  SELECT * FROM [dbo].[Bankleitzahlen] WHERE RecId = @mRI
GO

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Append_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Append_Bankleitzahlen]
   @Param_BLZ char(8)
  ,@Param_Bankname char(30)
  ,@Param_SWIFT_BIC char(11)
  ,@Param_Bankbez varchar(60)
  ,@Param_Plz char(5)
  ,@Param_Ort varchar(35)
  ,@Param_KzPz char(2)
  ,@Param_Pan char(5)
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_Bankleitzahlen()
      INSERT INTO [dbo].[ut_Bankleitzahlen] (RecId
        ,[BLZ]
        ,[Bankname]
        ,[SWIFT_BIC]
        ,[Bankbez]
        ,[Plz]
        ,[Ort]
        ,[KzPz]
        ,[Pan]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_BLZ))
        ,UPPER(RTRIM(@Param_Bankname))
        ,UPPER(RTRIM(@Param_SWIFT_BIC))
        ,RTRIM(@Param_Bankbez)
        ,UPPER(RTRIM(@Param_Plz))
        ,RTRIM(@Param_Ort)
        ,UPPER(RTRIM(@Param_KzPz))
        ,UPPER(RTRIM(@Param_Pan))
          )
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      SET @NeueId = 0
    END CATCH

    IF @NeueId <> 0
      SET @m_CountDown = 0
    ELSE
      SET @m_CountDown = @m_CountDown - 1

    IF @m_CountDown > 0
      WAITFOR DELAY '00:00:00.050'
  END -- WHILE
  SELECT @NeueId AS RecId
GO

-- insert with obsolete clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Add_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Add_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Add_Bankleitzahlen]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_Bankleitzahlen()
  INSERT INTO [dbo].[ut_Bankleitzahlen] (RecId) VALUES( @NeueId )
  IF @@ERROR = 0
    SELECT @NeueId AS RecId
  ELSE
    SELECT 0 AS RecId
GO

-- update with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Upd_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Upd_Bankleitzahlen] @mRI integer
  ,@Param_BLZ char(8)
  ,@Param_Bankname char(30)
  ,@Param_SWIFT_BIC char(11)
  ,@Param_Bankbez varchar(60)
  ,@Param_Plz char(5)
  ,@Param_Ort varchar(35)
  ,@Param_KzPz char(2)
  ,@Param_Pan char(5)
AS
  UPDATE [dbo].[ut_Bankleitzahlen] SET
    [BLZ] = UPPER(RTRIM(@Param_BLZ))
   ,[Bankname] = UPPER(RTRIM(@Param_Bankname))
   ,[SWIFT_BIC] = UPPER(RTRIM(@Param_SWIFT_BIC))
   ,[Bankbez] = RTRIM(@Param_Bankbez)
   ,[Plz] = UPPER(RTRIM(@Param_Plz))
   ,[Ort] = RTRIM(@Param_Ort)
   ,[KzPz] = UPPER(RTRIM(@Param_KzPz))
   ,[Pan] = UPPER(RTRIM(@Param_Pan))
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Upd2_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Upd2_Bankleitzahlen] @Param_RecId varchar(20)
  ,@Param_BLZ varchar(8)
  ,@Param_Bankname varchar(30)
  ,@Param_SWIFT_BIC varchar(11)
  ,@Param_Bankbez varchar(60)
  ,@Param_Plz varchar(5)
  ,@Param_Ort varchar(35)
  ,@Param_KzPz varchar(2)
  ,@Param_Pan varchar(5)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_Bankleitzahlen] SET
    [BLZ] = UPPER(RTRIM(@Param_BLZ))
   ,[Bankname] = UPPER(RTRIM(@Param_Bankname))
   ,[SWIFT_BIC] = UPPER(RTRIM(@Param_SWIFT_BIC))
   ,[Bankbez] = RTRIM(@Param_Bankbez)
   ,[Plz] = UPPER(RTRIM(@Param_Plz))
   ,[Ort] = RTRIM(@Param_Ort)
   ,[KzPz] = UPPER(RTRIM(@Param_KzPz))
   ,[Pan] = UPPER(RTRIM(@Param_Pan))
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_Bankleitzahlen] @mRI
GO

-- delete
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Del_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Del_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Del_Bankleitzahlen] @mRI integer
AS
  DELETE FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @mRI
GO

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_TrSel_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Bankleitzahlen]
AS
  SELECT RecId,
    LTRIM(RTRIM([BLZ]))
   +LTRIM(RTRIM([Bankname]))
   +LTRIM(RTRIM([SWIFT_BIC]))
   +LTRIM(RTRIM([Bankbez]))
   +LTRIM(RTRIM([Plz]))
   +LTRIM(RTRIM([Ort]))
   +LTRIM(RTRIM([KzPz]))
   +LTRIM(RTRIM([Pan]))
  AS Data FROM [dbo].[ut_Bankleitzahlen] WHERE RecId > 0 ORDER BY RecId
GO

-- reindex
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Reorg_Bankleitzahlen')
  DROP PROCEDURE [dbo].[up_Reorg_Bankleitzahlen]
GO
CREATE PROCEDURE [dbo].[up_Reorg_Bankleitzahlen] @bDbcc integer,@bRecId integer
AS
  IF (@bRecId = 1)
  BEGIN
    DECLARE  C        CURSOR FOR SELECT RecId FROM [dbo].[ut_Bankleitzahlen] 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_Bankleitzahlen] 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_Bankleitzahlen] )
  END
GO

-- logical key  ORDER BY BLZ,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Bankleitzahlen' and so.[id] = si.[id] and si.[name] = 'Ik_KEY00')
  DROP INDEX [Ik_KEY00] ON [dbo].[ut_Bankleitzahlen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Bankleitzahlen' and so.[id] = si.[id] and si.[name] = 'Ik_KEY00')
  CREATE INDEX [Ik_KEY00] ON [dbo].[ut_Bankleitzahlen] (BLZ,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_First_Bankleitzahlen_KEY00]
GO
CREATE PROCEDURE [dbo].[up_First_Bankleitzahlen_KEY00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00) ORDER BY BLZ,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Bankleitzahlen_KEY00')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Bankleitzahlen_KEY00] AS EXECUTE [dbo].[up_First_Bankleitzahlen_KEY00]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_Last_Bankleitzahlen_KEY00]
GO
CREATE PROCEDURE [dbo].[up_Last_Bankleitzahlen_KEY00] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00) ORDER BY BLZ desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Bankleitzahlen_KEY00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Bankleitzahlen_KEY00] AS EXECUTE [dbo].[up_Last_Bankleitzahlen_KEY00]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_Next_Bankleitzahlen_KEY00]
GO
CREATE PROCEDURE [dbo].[up_Next_Bankleitzahlen_KEY00] @oldRecId integer AS
  DECLARE @oldBLZ char(8)
  SELECT @oldBLZ = BLZ FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00)
    WHERE (BLZ = @oldBLZ) AND (RecId > @oldRecId)
    ORDER BY BLZ,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00)
    WHERE (BLZ > @oldBLZ)
    ORDER BY BLZ,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Bankleitzahlen_KEY00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Bankleitzahlen_KEY00] @oldRecId integer AS EXECUTE [dbo].[up_Next_Bankleitzahlen_KEY00] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_Prior_Bankleitzahlen_KEY00]
GO
CREATE PROCEDURE [dbo].[up_Prior_Bankleitzahlen_KEY00] @oldRecId integer AS
  DECLARE @oldBLZ char(8)
  SELECT @oldBLZ = BLZ FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00)
    WHERE (BLZ = @oldBLZ) AND (RecId < @oldRecId)
    ORDER BY BLZ desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00)
    WHERE (BLZ < @oldBLZ)
    ORDER BY BLZ desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Bankleitzahlen_KEY00')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Bankleitzahlen_KEY00] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Bankleitzahlen_KEY00] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_Next50_Bankleitzahlen_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[qp_Next50_Bankleitzahlen_KEY00]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_Prior50_Bankleitzahlen_KEY00]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[qp_Prior50_Bankleitzahlen_KEY00]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_FindF_Bankleitzahlen_KEY00]
GO
CREATE PROCEDURE [dbo].[up_FindF_Bankleitzahlen_KEY00] @xKey char(250), @xLen integer AS
  DECLARE @mBLZ char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,8)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00) WHERE 
      BLZ = @mBLZ
      ORDER BY BLZ,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00) WHERE 
      BLZ >= @mBLZ AND SUBSTRING(BLZ,1,LEN(@mBLZ)) = @mBLZ
      ORDER BY BLZ,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Bankleitzahlen_KEY00')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Bankleitzahlen_KEY00] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Bankleitzahlen_KEY00] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Bankleitzahlen_KEY00')
  DROP PROCEDURE [dbo].[up_FindL_Bankleitzahlen_KEY00]
GO
CREATE PROCEDURE [dbo].[up_FindL_Bankleitzahlen_KEY00] @xKey char(250), @xLen integer AS
  DECLARE @mBLZ char(8) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,8)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00) WHERE 
      BLZ = @mBLZ
      ORDER BY BLZ desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 8 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY00) WHERE 
      SUBSTRING(BLZ,1,LEN(@mBLZ)) = @mBLZ
      ORDER BY BLZ desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Bankleitzahlen_KEY00')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Bankleitzahlen_KEY00] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Bankleitzahlen_KEY00] @xKey, @xLen')
GO


-- logical key  ORDER BY Bankname,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Bankleitzahlen' and so.[id] = si.[id] and si.[name] = 'Ik_KEY01')
  DROP INDEX [Ik_KEY01] ON [dbo].[ut_Bankleitzahlen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Bankleitzahlen' and so.[id] = si.[id] and si.[name] = 'Ik_KEY01')
  CREATE INDEX [Ik_KEY01] ON [dbo].[ut_Bankleitzahlen] (Bankname,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_First_Bankleitzahlen_KEY01]
GO
CREATE PROCEDURE [dbo].[up_First_Bankleitzahlen_KEY01] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01) ORDER BY Bankname,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Bankleitzahlen_KEY01')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Bankleitzahlen_KEY01] AS EXECUTE [dbo].[up_First_Bankleitzahlen_KEY01]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_Last_Bankleitzahlen_KEY01]
GO
CREATE PROCEDURE [dbo].[up_Last_Bankleitzahlen_KEY01] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01) ORDER BY Bankname desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Bankleitzahlen_KEY01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Bankleitzahlen_KEY01] AS EXECUTE [dbo].[up_Last_Bankleitzahlen_KEY01]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_Next_Bankleitzahlen_KEY01]
GO
CREATE PROCEDURE [dbo].[up_Next_Bankleitzahlen_KEY01] @oldRecId integer AS
  DECLARE @oldBankname char(30)
  SELECT @oldBankname = Bankname FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01)
    WHERE (Bankname = @oldBankname) AND (RecId > @oldRecId)
    ORDER BY Bankname,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01)
    WHERE (Bankname > @oldBankname)
    ORDER BY Bankname,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Bankleitzahlen_KEY01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Bankleitzahlen_KEY01] @oldRecId integer AS EXECUTE [dbo].[up_Next_Bankleitzahlen_KEY01] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_Prior_Bankleitzahlen_KEY01]
GO
CREATE PROCEDURE [dbo].[up_Prior_Bankleitzahlen_KEY01] @oldRecId integer AS
  DECLARE @oldBankname char(30)
  SELECT @oldBankname = Bankname FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01)
    WHERE (Bankname = @oldBankname) AND (RecId < @oldRecId)
    ORDER BY Bankname desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01)
    WHERE (Bankname < @oldBankname)
    ORDER BY Bankname desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Bankleitzahlen_KEY01')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Bankleitzahlen_KEY01] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Bankleitzahlen_KEY01] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_Next50_Bankleitzahlen_KEY01]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[qp_Next50_Bankleitzahlen_KEY01]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_Prior50_Bankleitzahlen_KEY01]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[qp_Prior50_Bankleitzahlen_KEY01]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_FindF_Bankleitzahlen_KEY01]
GO
CREATE PROCEDURE [dbo].[up_FindF_Bankleitzahlen_KEY01] @xKey char(250), @xLen integer AS
  DECLARE @mBankname char(30) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,30)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 30 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01) WHERE 
      Bankname = @mBankname
      ORDER BY Bankname,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 30 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01) WHERE 
      Bankname >= @mBankname AND SUBSTRING(Bankname,1,LEN(@mBankname)) = @mBankname
      ORDER BY Bankname,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Bankleitzahlen_KEY01')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Bankleitzahlen_KEY01] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Bankleitzahlen_KEY01] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Bankleitzahlen_KEY01')
  DROP PROCEDURE [dbo].[up_FindL_Bankleitzahlen_KEY01]
GO
CREATE PROCEDURE [dbo].[up_FindL_Bankleitzahlen_KEY01] @xKey char(250), @xLen integer AS
  DECLARE @mBankname char(30) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,30)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 30 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01) WHERE 
      Bankname = @mBankname
      ORDER BY Bankname desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 30 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY01) WHERE 
      SUBSTRING(Bankname,1,LEN(@mBankname)) = @mBankname
      ORDER BY Bankname desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Bankleitzahlen_KEY01')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Bankleitzahlen_KEY01] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Bankleitzahlen_KEY01] @xKey, @xLen')
GO


-- logical key  ORDER BY SWIFT_BIC,RecId
IF 0 < (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Bankleitzahlen' and so.[id] = si.[id] and si.[name] = 'Ik_KEY02')
  DROP INDEX [Ik_KEY02] ON [dbo].[ut_Bankleitzahlen]
GO
IF 0 = (select count(*) from sysindexes si inner join sysobjects so on so.[xtype] = 'U' and so.[name] = 'ut_Bankleitzahlen' and so.[id] = si.[id] and si.[name] = 'Ik_KEY02')
  CREATE INDEX [Ik_KEY02] ON [dbo].[ut_Bankleitzahlen] (SWIFT_BIC,RecId)
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_First_Bankleitzahlen_KEY02]
GO
CREATE PROCEDURE [dbo].[up_First_Bankleitzahlen_KEY02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02) ORDER BY SWIFT_BIC,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Bankleitzahlen_KEY02')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Bankleitzahlen_KEY02] AS EXECUTE [dbo].[up_First_Bankleitzahlen_KEY02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_Last_Bankleitzahlen_KEY02]
GO
CREATE PROCEDURE [dbo].[up_Last_Bankleitzahlen_KEY02] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02) ORDER BY SWIFT_BIC desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Bankleitzahlen_KEY02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Bankleitzahlen_KEY02] AS EXECUTE [dbo].[up_Last_Bankleitzahlen_KEY02]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_Next_Bankleitzahlen_KEY02]
GO
CREATE PROCEDURE [dbo].[up_Next_Bankleitzahlen_KEY02] @oldRecId integer AS
  DECLARE @oldSWIFT_BIC char(11)
  SELECT @oldSWIFT_BIC = SWIFT_BIC FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02)
    WHERE (SWIFT_BIC > @oldSWIFT_BIC)
    ORDER BY SWIFT_BIC,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next_Bankleitzahlen_KEY02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Bankleitzahlen_KEY02] @oldRecId integer AS EXECUTE [dbo].[up_Next_Bankleitzahlen_KEY02] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_Prior_Bankleitzahlen_KEY02]
GO
CREATE PROCEDURE [dbo].[up_Prior_Bankleitzahlen_KEY02] @oldRecId integer AS
  DECLARE @oldSWIFT_BIC char(11)
  SELECT @oldSWIFT_BIC = SWIFT_BIC FROM [dbo].[ut_Bankleitzahlen] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02)
    WHERE (SWIFT_BIC < @oldSWIFT_BIC)
    ORDER BY SWIFT_BIC desc,RecId desc
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior_Bankleitzahlen_KEY02')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Bankleitzahlen_KEY02] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Bankleitzahlen_KEY02] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next50_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_Next50_Bankleitzahlen_KEY02]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Next50_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[qp_Next50_Bankleitzahlen_KEY02]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior50_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_Prior50_Bankleitzahlen_KEY02]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Prior50_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[qp_Prior50_Bankleitzahlen_KEY02]
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindF_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_FindF_Bankleitzahlen_KEY02]
GO
CREATE PROCEDURE [dbo].[up_FindF_Bankleitzahlen_KEY02] @xKey char(250), @xLen integer AS
  DECLARE @mSWIFT_BIC char(11) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,11)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02) WHERE 
      SWIFT_BIC = @mSWIFT_BIC
      ORDER BY SWIFT_BIC,RecId
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02) WHERE 
      SWIFT_BIC >= @mSWIFT_BIC AND SUBSTRING(SWIFT_BIC,1,LEN(@mSWIFT_BIC)) = @mSWIFT_BIC
      ORDER BY SWIFT_BIC,RecId
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindF_Bankleitzahlen_KEY02')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindF_Bankleitzahlen_KEY02] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindF_Bankleitzahlen_KEY02] @xKey, @xLen')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_FindL_Bankleitzahlen_KEY02')
  DROP PROCEDURE [dbo].[up_FindL_Bankleitzahlen_KEY02]
GO
CREATE PROCEDURE [dbo].[up_FindL_Bankleitzahlen_KEY02] @xKey char(250), @xLen integer AS
  DECLARE @mSWIFT_BIC char(11) = dbo.itcfunc_KeyToken_varchar(@xKey,@xLen,1,11)

  -- pass 1 -- Abfrage mit vollständigen Feldern (absteigende Vergleichslänge)

  IF @xLen = 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02) WHERE 
      SWIFT_BIC = @mSWIFT_BIC
      ORDER BY SWIFT_BIC desc,RecId desc
    RETURN
  END

  -- pass 2 -- Abfrage mit teilweise unkompletten Felder (aufsteigende Vergleichslänge)

  IF @xLen < 11 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Bankleitzahlen] WITH(INDEX = Ik_KEY02) WHERE 
      SUBSTRING(SWIFT_BIC,1,LEN(@mSWIFT_BIC)) = @mSWIFT_BIC
      ORDER BY SWIFT_BIC desc,RecId desc
    RETURN
  END

  SELECT 0 AS RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_FindL_Bankleitzahlen_KEY02')
  EXEC('ALTER PROCEDURE [dbo].[qp_FindL_Bankleitzahlen_KEY02] @xKey char(250), @xLen integer AS EXECUTE [dbo].[up_FindL_Bankleitzahlen_KEY02] @xKey, @xLen')
GO


