-- This file is part of the it-crafts software and is subject to copyright protection.
-- (C) 2022..2025, 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_Anschriften')
BEGIN
CREATE TABLE [dbo].[ut_Anschriften]
(
  [RecId]  integer NOT NULL
 ,[Nr] char(2) DEFAULT ''
 ,[Name1] varchar(50) DEFAULT ''
 ,[Name2] varchar(50) DEFAULT ''
 ,[Name3] varchar(50) DEFAULT ''
 ,[Postfach] varchar(30) DEFAULT ''
 ,[Strasse] varchar(50) DEFAULT ''
 ,[PlzOrt] varchar(50) DEFAULT ''
 ,[Telefon] varchar(30) DEFAULT ''
 ,[FaxNr] varchar(30) DEFAULT ''
 ,[Kurzname] char(20) DEFAULT ''
 ,[Bank1_Name] varchar(30) DEFAULT ''
 ,[Bank1_BLZ] varchar(11) DEFAULT ''
 ,[Bank1_Konto] varchar(15) DEFAULT ''
 ,[Bank2_Name] varchar(30) DEFAULT ''
 ,[Bank2_BLZ] varchar(11) DEFAULT ''
 ,[Bank2_Konto] varchar(15) DEFAULT ''
 ,[Bank3_Name] varchar(30) DEFAULT ''
 ,[Bank3_BLZ] varchar(11) DEFAULT ''
 ,[Bank3_Konto] varchar(15) DEFAULT ''
 ,[SWIFT_BIC1] char(11) DEFAULT ''
 ,[IBAN1] char(34) DEFAULT ''
 ,[SWIFT_BIC2] char(11) DEFAULT ''
 ,[IBAN2] char(34) DEFAULT ''
 ,[SWIFT_BIC3] char(11) DEFAULT ''
 ,[IBAN3] char(34) DEFAULT ''
 ,[UstId1] char(15) DEFAULT ''
 ,[PlichtAn1] varchar(120) DEFAULT ''
 ,[PlichtAn2] varchar(120) DEFAULT ''
 ,[PlichtAn3] varchar(120) DEFAULT ''
 ,[KontoInh1] varchar(40) DEFAULT ''
 ,[KontoInh2] varchar(40) DEFAULT ''
 ,[KontoInh3] varchar(40) DEFAULT ''
 ,[PeppolAddr] varchar(40) DEFAULT ''
  CONSTRAINT [Pk_Anschriften]  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_Anschriften_KeyDel')
  DROP TRIGGER [dbo].[tr_Anschriften_KeyDel]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'TR'   and [name] = 'tr_Anschriften_KeyUpd')
  DROP TRIGGER [dbo].[tr_Anschriften_KeyUpd]
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'U' and [name] = 'ut_Anschriften_Keys')
  DROP TABLE [dbo].[ut_Anschriften_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_Anschriften' and sc.[name] = 'Nr')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Nr] 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_Anschriften' and sc.[name] = 'Name1')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Name1] varchar(50) 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_Anschriften' and sc.[name] = 'Name2')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Name2] varchar(50) 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_Anschriften' and sc.[name] = 'Name3')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Name3] varchar(50) 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_Anschriften' and sc.[name] = 'Postfach')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Postfach] varchar(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_Anschriften' and sc.[name] = 'Strasse')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Strasse] varchar(50) 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_Anschriften' and sc.[name] = 'PlzOrt')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [PlzOrt] varchar(50) 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_Anschriften' and sc.[name] = 'Telefon')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Telefon] varchar(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_Anschriften' and sc.[name] = 'FaxNr')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [FaxNr] varchar(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_Anschriften' and sc.[name] = 'Kurzname')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Kurzname] char(20) 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_Anschriften' and sc.[name] = 'Bank1_Name')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank1_Name] varchar(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_Anschriften' and sc.[name] = 'Bank1_BLZ')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank1_BLZ] varchar(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_Anschriften' and sc.[name] = 'Bank1_Konto')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank1_Konto] varchar(15) 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_Anschriften' and sc.[name] = 'Bank2_Name')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank2_Name] varchar(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_Anschriften' and sc.[name] = 'Bank2_BLZ')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank2_BLZ] varchar(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_Anschriften' and sc.[name] = 'Bank2_Konto')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank2_Konto] varchar(15) 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_Anschriften' and sc.[name] = 'Bank3_Name')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank3_Name] varchar(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_Anschriften' and sc.[name] = 'Bank3_BLZ')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank3_BLZ] varchar(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_Anschriften' and sc.[name] = 'Bank3_Konto')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [Bank3_Konto] varchar(15) 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_Anschriften' and sc.[name] = 'SWIFT_BIC1')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [SWIFT_BIC1] 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_Anschriften' and sc.[name] = 'IBAN1')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [IBAN1] char(34) 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_Anschriften' and sc.[name] = 'SWIFT_BIC2')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [SWIFT_BIC2] 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_Anschriften' and sc.[name] = 'IBAN2')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [IBAN2] char(34) 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_Anschriften' and sc.[name] = 'SWIFT_BIC3')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [SWIFT_BIC3] 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_Anschriften' and sc.[name] = 'IBAN3')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [IBAN3] char(34) 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_Anschriften' and sc.[name] = 'UstId1')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [UstId1] char(15) 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_Anschriften' and sc.[name] = 'PlichtAn1')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [PlichtAn1] varchar(120) 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_Anschriften' and sc.[name] = 'PlichtAn2')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [PlichtAn2] varchar(120) 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_Anschriften' and sc.[name] = 'PlichtAn3')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [PlichtAn3] varchar(120) 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_Anschriften' and sc.[name] = 'KontoInh1')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [KontoInh1] varchar(40) 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_Anschriften' and sc.[name] = 'KontoInh2')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [KontoInh2] varchar(40) 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_Anschriften' and sc.[name] = 'KontoInh3')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [KontoInh3] varchar(40) 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_Anschriften' and sc.[name] = 'PeppolAddr')
BEGIN
  ALTER TABLE [dbo].[ut_Anschriften] ADD [PeppolAddr] varchar(40) NOT NULL DEFAULT ''
END
GO

-- check lengths of text fields
IF (2 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Nr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Nr'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Nr] char(2)
END
GO
IF (2 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Nr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Nr'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Nr] varchar(2)
END
GO
IF (50 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Name1] char(50)
END
GO
IF (50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Name1] varchar(50)
END
GO
IF (50 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Name2] char(50)
END
GO
IF (50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Name2] varchar(50)
END
GO
IF (50 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name3'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Name3] char(50)
END
GO
IF (50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name3'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Name3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Name3] varchar(50)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Postfach'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Postfach'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Postfach] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Postfach'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Postfach'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Postfach] varchar(30)
END
GO
IF (50 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Strasse'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Strasse'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Strasse] char(50)
END
GO
IF (50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Strasse'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Strasse'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Strasse] varchar(50)
END
GO
IF (50 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlzOrt'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlzOrt'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlzOrt] char(50)
END
GO
IF (50 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlzOrt'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlzOrt'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlzOrt] varchar(50)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Telefon'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Telefon'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Telefon] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Telefon'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Telefon'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Telefon] varchar(30)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'FaxNr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'FaxNr'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [FaxNr] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'FaxNr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'FaxNr'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [FaxNr] varchar(30)
END
GO
IF (20 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Kurzname'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Kurzname'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Kurzname] char(20)
END
GO
IF (20 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Kurzname'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Kurzname'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Kurzname] varchar(20)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Name'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Name'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank1_Name] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Name'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Name'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank1_Name] 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_Anschriften' and sc.[name] = 'Bank1_BLZ'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank1_BLZ] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_BLZ'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank1_BLZ] varchar(11)
END
GO
IF (15 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Konto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Konto'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank1_Konto] char(15)
END
GO
IF (15 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Konto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank1_Konto'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank1_Konto] varchar(15)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Name'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Name'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank2_Name] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Name'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Name'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank2_Name] 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_Anschriften' and sc.[name] = 'Bank2_BLZ'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank2_BLZ] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_BLZ'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank2_BLZ] varchar(11)
END
GO
IF (15 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Konto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Konto'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank2_Konto] char(15)
END
GO
IF (15 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Konto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank2_Konto'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank2_Konto] varchar(15)
END
GO
IF (30 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Name'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Name'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank3_Name] char(30)
END
GO
IF (30 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Name'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Name'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank3_Name] 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_Anschriften' and sc.[name] = 'Bank3_BLZ'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank3_BLZ] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_BLZ'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_BLZ'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank3_BLZ] varchar(11)
END
GO
IF (15 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Konto'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Konto'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank3_Konto] char(15)
END
GO
IF (15 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Konto'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'Bank3_Konto'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [Bank3_Konto] varchar(15)
END
GO
IF (11 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [SWIFT_BIC1] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [SWIFT_BIC1] varchar(11)
END
GO
IF (34 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [IBAN1] char(34)
END
GO
IF (34 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [IBAN1] varchar(34)
END
GO
IF (11 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [SWIFT_BIC2] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [SWIFT_BIC2] varchar(11)
END
GO
IF (34 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [IBAN2] char(34)
END
GO
IF (34 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [IBAN2] varchar(34)
END
GO
IF (11 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC3'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [SWIFT_BIC3] char(11)
END
GO
IF (11 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC3'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'SWIFT_BIC3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [SWIFT_BIC3] varchar(11)
END
GO
IF (34 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN3'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [IBAN3] char(34)
END
GO
IF (34 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN3'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'IBAN3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [IBAN3] varchar(34)
END
GO
IF (15 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'UstId1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'UstId1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [UstId1] char(15)
END
GO
IF (15 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'UstId1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'UstId1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [UstId1] varchar(15)
END
GO
IF (120 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlichtAn1] char(120)
END
GO
IF (120 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlichtAn1] varchar(120)
END
GO
IF (120 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlichtAn2] char(120)
END
GO
IF (120 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlichtAn2] varchar(120)
END
GO
IF (120 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn3'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlichtAn3] char(120)
END
GO
IF (120 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn3'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PlichtAn3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PlichtAn3] varchar(120)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh1'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [KontoInh1] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh1'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh1'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [KontoInh1] varchar(40)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh2'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [KontoInh2] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh2'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh2'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [KontoInh2] varchar(40)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh3'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [KontoInh3] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh3'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'KontoInh3'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [KontoInh3] varchar(40)
END
GO
IF (40 > (select sc.length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PeppolAddr'))
  AND (175 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PeppolAddr'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PeppolAddr] char(40)
END
GO
IF (40 > (select length from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PeppolAddr'))
  AND (167 = (select sc.xtype from syscolumns sc inner join sysobjects so on sc.[id] = so.[id] and so.[name] = 'ut_Anschriften' and sc.[name] = 'PeppolAddr'))
BEGIN
   ALTER TABLE [dbo].[ut_Anschriften] ALTER COLUMN [PeppolAddr] varchar(40)
END
GO

-- create dedicated view
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'V' and [name] = 'Anschriften')
  DROP VIEW [dbo].[Anschriften]
GO
CREATE VIEW [dbo].[Anschriften] AS
  SELECT [RecId]
 ,[Nr]
 ,[Name1]
 ,[Name2]
 ,[Name3]
 ,[Postfach]
 ,[Strasse]
 ,[PlzOrt]
 ,[Telefon]
 ,[FaxNr]
 ,[Kurzname]
 ,[Bank1_Name]
 ,[Bank1_BLZ]
 ,[Bank1_Konto]
 ,[Bank2_Name]
 ,[Bank2_BLZ]
 ,[Bank2_Konto]
 ,[Bank3_Name]
 ,[Bank3_BLZ]
 ,[Bank3_Konto]
 ,[SWIFT_BIC1]
 ,[IBAN1]
 ,[SWIFT_BIC2]
 ,[IBAN2]
 ,[SWIFT_BIC3]
 ,[IBAN3]
 ,[UstId1]
 ,[PlichtAn1]
 ,[PlichtAn2]
 ,[PlichtAn3]
 ,[KontoInh1]
 ,[KontoInh2]
 ,[KontoInh3]
 ,[PeppolAddr]
  FROM [dbo].[ut_Anschriften]
GO


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

-- select with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Anschriften')
  DROP PROCEDURE [dbo].[up_Sel_Anschriften]
GO
CREATE PROCEDURE [dbo].[up_Sel_Anschriften]  @mRI integer
AS
  SELECT
    [Nr]
   ,[Name1]
   ,[Name2]
   ,[Name3]
   ,[Postfach]
   ,[Strasse]
   ,[PlzOrt]
   ,[Telefon]
   ,[FaxNr]
   ,[Kurzname]
   ,[Bank1_Name]
   ,[Bank1_BLZ]
   ,[Bank1_Konto]
   ,[Bank2_Name]
   ,[Bank2_BLZ]
   ,[Bank2_Konto]
   ,[Bank3_Name]
   ,[Bank3_BLZ]
   ,[Bank3_Konto]
   ,[SWIFT_BIC1]
   ,[IBAN1]
   ,[SWIFT_BIC2]
   ,[IBAN2]
   ,[SWIFT_BIC3]
   ,[IBAN3]
   ,[UstId1]
   ,[PlichtAn1]
   ,[PlichtAn2]
   ,[PlichtAn3]
   ,[KontoInh1]
   ,[KontoInh2]
   ,[KontoInh3]
   ,[PeppolAddr]
  FROM [dbo].[ut_Anschriften] WHERE RecId = @mRI
GO

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

-- insert with classic client
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Append_Anschriften')
  DROP PROCEDURE [dbo].[up_Append_Anschriften]
GO
CREATE PROCEDURE [dbo].[up_Append_Anschriften]
   @Param_Nr char(2)
  ,@Param_Name1 varchar(50)
  ,@Param_Name2 varchar(50)
  ,@Param_Name3 varchar(50)
  ,@Param_Postfach varchar(30)
  ,@Param_Strasse varchar(50)
  ,@Param_PlzOrt varchar(50)
  ,@Param_Telefon varchar(30)
  ,@Param_FaxNr varchar(30)
  ,@Param_Kurzname char(20)
  ,@Param_Bank1_Name varchar(30)
  ,@Param_Bank1_BLZ varchar(11)
  ,@Param_Bank1_Konto varchar(15)
  ,@Param_Bank2_Name varchar(30)
  ,@Param_Bank2_BLZ varchar(11)
  ,@Param_Bank2_Konto varchar(15)
  ,@Param_Bank3_Name varchar(30)
  ,@Param_Bank3_BLZ varchar(11)
  ,@Param_Bank3_Konto varchar(15)
  ,@Param_SWIFT_BIC1 char(11)
  ,@Param_IBAN1 char(34)
  ,@Param_SWIFT_BIC2 char(11)
  ,@Param_IBAN2 char(34)
  ,@Param_SWIFT_BIC3 char(11)
  ,@Param_IBAN3 char(34)
  ,@Param_UstId1 char(15)
  ,@Param_PlichtAn1 varchar(120)
  ,@Param_PlichtAn2 varchar(120)
  ,@Param_PlichtAn3 varchar(120)
  ,@Param_KontoInh1 varchar(40)
  ,@Param_KontoInh2 varchar(40)
  ,@Param_KontoInh3 varchar(40)
  ,@Param_PeppolAddr varchar(40)
AS
  DECLARE @NeueId integer
  DECLARE @m_CountDown integer = 10
  WHILE @m_CountDown > 0 BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
      SET @NeueId = dbo.itcfunc_NextRecId_Anschriften()
      INSERT INTO [dbo].[ut_Anschriften] (RecId
        ,[Nr]
        ,[Name1]
        ,[Name2]
        ,[Name3]
        ,[Postfach]
        ,[Strasse]
        ,[PlzOrt]
        ,[Telefon]
        ,[FaxNr]
        ,[Kurzname]
        ,[Bank1_Name]
        ,[Bank1_BLZ]
        ,[Bank1_Konto]
        ,[Bank2_Name]
        ,[Bank2_BLZ]
        ,[Bank2_Konto]
        ,[Bank3_Name]
        ,[Bank3_BLZ]
        ,[Bank3_Konto]
        ,[SWIFT_BIC1]
        ,[IBAN1]
        ,[SWIFT_BIC2]
        ,[IBAN2]
        ,[SWIFT_BIC3]
        ,[IBAN3]
        ,[UstId1]
        ,[PlichtAn1]
        ,[PlichtAn2]
        ,[PlichtAn3]
        ,[KontoInh1]
        ,[KontoInh2]
        ,[KontoInh3]
        ,[PeppolAddr]
          ) VALUES (@NeueId
        ,UPPER(RTRIM(@Param_Nr))
        ,RTRIM(@Param_Name1)
        ,RTRIM(@Param_Name2)
        ,RTRIM(@Param_Name3)
        ,RTRIM(@Param_Postfach)
        ,RTRIM(@Param_Strasse)
        ,RTRIM(@Param_PlzOrt)
        ,RTRIM(@Param_Telefon)
        ,RTRIM(@Param_FaxNr)
        ,UPPER(RTRIM(@Param_Kurzname))
        ,RTRIM(@Param_Bank1_Name)
        ,RTRIM(@Param_Bank1_BLZ)
        ,RTRIM(@Param_Bank1_Konto)
        ,RTRIM(@Param_Bank2_Name)
        ,RTRIM(@Param_Bank2_BLZ)
        ,RTRIM(@Param_Bank2_Konto)
        ,RTRIM(@Param_Bank3_Name)
        ,RTRIM(@Param_Bank3_BLZ)
        ,RTRIM(@Param_Bank3_Konto)
        ,UPPER(RTRIM(@Param_SWIFT_BIC1))
        ,UPPER(RTRIM(@Param_IBAN1))
        ,UPPER(RTRIM(@Param_SWIFT_BIC2))
        ,UPPER(RTRIM(@Param_IBAN2))
        ,UPPER(RTRIM(@Param_SWIFT_BIC3))
        ,UPPER(RTRIM(@Param_IBAN3))
        ,UPPER(RTRIM(@Param_UstId1))
        ,RTRIM(@Param_PlichtAn1)
        ,RTRIM(@Param_PlichtAn2)
        ,RTRIM(@Param_PlichtAn3)
        ,RTRIM(@Param_KontoInh1)
        ,RTRIM(@Param_KontoInh2)
        ,RTRIM(@Param_KontoInh3)
        ,RTRIM(@Param_PeppolAddr)
          )
      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_Anschriften')
  DROP PROCEDURE [dbo].[up_Add_Anschriften]
GO
CREATE PROCEDURE [dbo].[up_Add_Anschriften]
AS
  DECLARE @NeueId integer = dbo.itcfunc_NextRecId_Anschriften()
  INSERT INTO [dbo].[ut_Anschriften] (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_Anschriften')
  DROP PROCEDURE [dbo].[up_Upd_Anschriften]
GO
CREATE PROCEDURE [dbo].[up_Upd_Anschriften] @mRI integer
  ,@Param_Nr char(2)
  ,@Param_Name1 varchar(50)
  ,@Param_Name2 varchar(50)
  ,@Param_Name3 varchar(50)
  ,@Param_Postfach varchar(30)
  ,@Param_Strasse varchar(50)
  ,@Param_PlzOrt varchar(50)
  ,@Param_Telefon varchar(30)
  ,@Param_FaxNr varchar(30)
  ,@Param_Kurzname char(20)
  ,@Param_Bank1_Name varchar(30)
  ,@Param_Bank1_BLZ varchar(11)
  ,@Param_Bank1_Konto varchar(15)
  ,@Param_Bank2_Name varchar(30)
  ,@Param_Bank2_BLZ varchar(11)
  ,@Param_Bank2_Konto varchar(15)
  ,@Param_Bank3_Name varchar(30)
  ,@Param_Bank3_BLZ varchar(11)
  ,@Param_Bank3_Konto varchar(15)
  ,@Param_SWIFT_BIC1 char(11)
  ,@Param_IBAN1 char(34)
  ,@Param_SWIFT_BIC2 char(11)
  ,@Param_IBAN2 char(34)
  ,@Param_SWIFT_BIC3 char(11)
  ,@Param_IBAN3 char(34)
  ,@Param_UstId1 char(15)
  ,@Param_PlichtAn1 varchar(120)
  ,@Param_PlichtAn2 varchar(120)
  ,@Param_PlichtAn3 varchar(120)
  ,@Param_KontoInh1 varchar(40)
  ,@Param_KontoInh2 varchar(40)
  ,@Param_KontoInh3 varchar(40)
  ,@Param_PeppolAddr varchar(40)
AS
  UPDATE [dbo].[ut_Anschriften] SET
    [Nr] = UPPER(RTRIM(@Param_Nr))
   ,[Name1] = RTRIM(@Param_Name1)
   ,[Name2] = RTRIM(@Param_Name2)
   ,[Name3] = RTRIM(@Param_Name3)
   ,[Postfach] = RTRIM(@Param_Postfach)
   ,[Strasse] = RTRIM(@Param_Strasse)
   ,[PlzOrt] = RTRIM(@Param_PlzOrt)
   ,[Telefon] = RTRIM(@Param_Telefon)
   ,[FaxNr] = RTRIM(@Param_FaxNr)
   ,[Kurzname] = UPPER(RTRIM(@Param_Kurzname))
   ,[Bank1_Name] = RTRIM(@Param_Bank1_Name)
   ,[Bank1_BLZ] = RTRIM(@Param_Bank1_BLZ)
   ,[Bank1_Konto] = RTRIM(@Param_Bank1_Konto)
   ,[Bank2_Name] = RTRIM(@Param_Bank2_Name)
   ,[Bank2_BLZ] = RTRIM(@Param_Bank2_BLZ)
   ,[Bank2_Konto] = RTRIM(@Param_Bank2_Konto)
   ,[Bank3_Name] = RTRIM(@Param_Bank3_Name)
   ,[Bank3_BLZ] = RTRIM(@Param_Bank3_BLZ)
   ,[Bank3_Konto] = RTRIM(@Param_Bank3_Konto)
   ,[SWIFT_BIC1] = UPPER(RTRIM(@Param_SWIFT_BIC1))
   ,[IBAN1] = UPPER(RTRIM(@Param_IBAN1))
   ,[SWIFT_BIC2] = UPPER(RTRIM(@Param_SWIFT_BIC2))
   ,[IBAN2] = UPPER(RTRIM(@Param_IBAN2))
   ,[SWIFT_BIC3] = UPPER(RTRIM(@Param_SWIFT_BIC3))
   ,[IBAN3] = UPPER(RTRIM(@Param_IBAN3))
   ,[UstId1] = UPPER(RTRIM(@Param_UstId1))
   ,[PlichtAn1] = RTRIM(@Param_PlichtAn1)
   ,[PlichtAn2] = RTRIM(@Param_PlichtAn2)
   ,[PlichtAn3] = RTRIM(@Param_PlichtAn3)
   ,[KontoInh1] = RTRIM(@Param_KontoInh1)
   ,[KontoInh2] = RTRIM(@Param_KontoInh2)
   ,[KontoInh3] = RTRIM(@Param_KontoInh3)
   ,[PeppolAddr] = RTRIM(@Param_PeppolAddr)
  WHERE RecId = @mRI
GO

-- update with modern clients
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Upd2_Anschriften')
  DROP PROCEDURE [dbo].[up_Upd2_Anschriften]
GO
CREATE PROCEDURE [dbo].[up_Upd2_Anschriften] @Param_RecId varchar(20)
  ,@Param_Nr varchar(2)
  ,@Param_Name1 varchar(50)
  ,@Param_Name2 varchar(50)
  ,@Param_Name3 varchar(50)
  ,@Param_Postfach varchar(30)
  ,@Param_Strasse varchar(50)
  ,@Param_PlzOrt varchar(50)
  ,@Param_Telefon varchar(30)
  ,@Param_FaxNr varchar(30)
  ,@Param_Kurzname varchar(20)
  ,@Param_Bank1_Name varchar(30)
  ,@Param_Bank1_BLZ varchar(11)
  ,@Param_Bank1_Konto varchar(15)
  ,@Param_Bank2_Name varchar(30)
  ,@Param_Bank2_BLZ varchar(11)
  ,@Param_Bank2_Konto varchar(15)
  ,@Param_Bank3_Name varchar(30)
  ,@Param_Bank3_BLZ varchar(11)
  ,@Param_Bank3_Konto varchar(15)
  ,@Param_SWIFT_BIC1 varchar(11)
  ,@Param_IBAN1 varchar(34)
  ,@Param_SWIFT_BIC2 varchar(11)
  ,@Param_IBAN2 varchar(34)
  ,@Param_SWIFT_BIC3 varchar(11)
  ,@Param_IBAN3 varchar(34)
  ,@Param_UstId1 varchar(15)
  ,@Param_PlichtAn1 varchar(120)
  ,@Param_PlichtAn2 varchar(120)
  ,@Param_PlichtAn3 varchar(120)
  ,@Param_KontoInh1 varchar(40)
  ,@Param_KontoInh2 varchar(40)
  ,@Param_KontoInh3 varchar(40)
  ,@Param_PeppolAddr varchar(40)
AS
  DECLARE @mRI integer = dbo.itcfunc_integer(@Param_RecId)
  UPDATE [dbo].[ut_Anschriften] SET
    [Nr] = UPPER(RTRIM(@Param_Nr))
   ,[Name1] = RTRIM(@Param_Name1)
   ,[Name2] = RTRIM(@Param_Name2)
   ,[Name3] = RTRIM(@Param_Name3)
   ,[Postfach] = RTRIM(@Param_Postfach)
   ,[Strasse] = RTRIM(@Param_Strasse)
   ,[PlzOrt] = RTRIM(@Param_PlzOrt)
   ,[Telefon] = RTRIM(@Param_Telefon)
   ,[FaxNr] = RTRIM(@Param_FaxNr)
   ,[Kurzname] = UPPER(RTRIM(@Param_Kurzname))
   ,[Bank1_Name] = RTRIM(@Param_Bank1_Name)
   ,[Bank1_BLZ] = RTRIM(@Param_Bank1_BLZ)
   ,[Bank1_Konto] = RTRIM(@Param_Bank1_Konto)
   ,[Bank2_Name] = RTRIM(@Param_Bank2_Name)
   ,[Bank2_BLZ] = RTRIM(@Param_Bank2_BLZ)
   ,[Bank2_Konto] = RTRIM(@Param_Bank2_Konto)
   ,[Bank3_Name] = RTRIM(@Param_Bank3_Name)
   ,[Bank3_BLZ] = RTRIM(@Param_Bank3_BLZ)
   ,[Bank3_Konto] = RTRIM(@Param_Bank3_Konto)
   ,[SWIFT_BIC1] = UPPER(RTRIM(@Param_SWIFT_BIC1))
   ,[IBAN1] = UPPER(RTRIM(@Param_IBAN1))
   ,[SWIFT_BIC2] = UPPER(RTRIM(@Param_SWIFT_BIC2))
   ,[IBAN2] = UPPER(RTRIM(@Param_IBAN2))
   ,[SWIFT_BIC3] = UPPER(RTRIM(@Param_SWIFT_BIC3))
   ,[IBAN3] = UPPER(RTRIM(@Param_IBAN3))
   ,[UstId1] = UPPER(RTRIM(@Param_UstId1))
   ,[PlichtAn1] = RTRIM(@Param_PlichtAn1)
   ,[PlichtAn2] = RTRIM(@Param_PlichtAn2)
   ,[PlichtAn3] = RTRIM(@Param_PlichtAn3)
   ,[KontoInh1] = RTRIM(@Param_KontoInh1)
   ,[KontoInh2] = RTRIM(@Param_KontoInh2)
   ,[KontoInh3] = RTRIM(@Param_KontoInh3)
   ,[PeppolAddr] = RTRIM(@Param_PeppolAddr)
  WHERE RecId = @mRI

  EXECUTE [dbo].[up_Sel2_Anschriften] @mRI
GO

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

-- text index
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_TrSel_Anschriften')
  DROP PROCEDURE [dbo].[up_TrSel_Anschriften]
GO
CREATE PROCEDURE [dbo].[up_TrSel_Anschriften]
AS
  SELECT RecId,
    LTRIM(RTRIM([Nr]))
   +LTRIM(RTRIM([Name1]))
   +LTRIM(RTRIM([Name2]))
   +LTRIM(RTRIM([Name3]))
   +LTRIM(RTRIM([Postfach]))
   +LTRIM(RTRIM([Strasse]))
   +LTRIM(RTRIM([PlzOrt]))
   +LTRIM(RTRIM([Telefon]))
   +LTRIM(RTRIM([FaxNr]))
   +LTRIM(RTRIM([Kurzname]))
   +LTRIM(RTRIM([Bank1_Name]))
   +LTRIM(RTRIM([Bank1_BLZ]))
   +LTRIM(RTRIM([Bank1_Konto]))
   +LTRIM(RTRIM([Bank2_Name]))
   +LTRIM(RTRIM([Bank2_BLZ]))
   +LTRIM(RTRIM([Bank2_Konto]))
   +LTRIM(RTRIM([Bank3_Name]))
   +LTRIM(RTRIM([Bank3_BLZ]))
   +LTRIM(RTRIM([Bank3_Konto]))
   +LTRIM(RTRIM([SWIFT_BIC1]))
   +LTRIM(RTRIM([IBAN1]))
   +LTRIM(RTRIM([SWIFT_BIC2]))
   +LTRIM(RTRIM([IBAN2]))
   +LTRIM(RTRIM([SWIFT_BIC3]))
   +LTRIM(RTRIM([IBAN3]))
   +LTRIM(RTRIM([UstId1]))
   +LTRIM(RTRIM([PlichtAn1]))
   +LTRIM(RTRIM([PlichtAn2]))
   +LTRIM(RTRIM([PlichtAn3]))
   +LTRIM(RTRIM([KontoInh1]))
   +LTRIM(RTRIM([KontoInh2]))
   +LTRIM(RTRIM([KontoInh3]))
   +LTRIM(RTRIM([PeppolAddr]))
  AS Data FROM [dbo].[ut_Anschriften] WHERE RecId > 0 ORDER BY RecId
GO

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

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

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_First_Anschriften_Primary')
  DROP PROCEDURE [dbo].[up_First_Anschriften_Primary]
GO
CREATE PROCEDURE [dbo].[up_First_Anschriften_Primary] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary) ORDER BY Nr,RecId
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_First_Anschriften_Primary')
  EXEC('ALTER PROCEDURE [dbo].[qp_First_Anschriften_Primary] AS EXECUTE [dbo].[up_First_Anschriften_Primary]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Last_Anschriften_Primary')
  DROP PROCEDURE [dbo].[up_Last_Anschriften_Primary]
GO
CREATE PROCEDURE [dbo].[up_Last_Anschriften_Primary] AS
  SELECT TOP 1 RecId FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary) ORDER BY Nr desc,RecId desc
GO
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'qp_Last_Anschriften_Primary')
  EXEC('ALTER PROCEDURE [dbo].[qp_Last_Anschriften_Primary] AS EXECUTE [dbo].[up_Last_Anschriften_Primary]')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Next_Anschriften_Primary')
  DROP PROCEDURE [dbo].[up_Next_Anschriften_Primary]
GO
CREATE PROCEDURE [dbo].[up_Next_Anschriften_Primary] @oldRecId integer AS
  DECLARE @oldNr char(2)
  SELECT @oldNr = Nr FROM [dbo].[ut_Anschriften] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary)
    WHERE (Nr = @oldNr) AND (RecId > @oldRecId)
    ORDER BY Nr,RecId
  IF @newRecId <> 0 BEGIN
    SELECT @newRecId AS RecId
    RETURN
  END

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary)
    WHERE (Nr > @oldNr)
    ORDER BY Nr,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_Anschriften_Primary')
  EXEC('ALTER PROCEDURE [dbo].[qp_Next_Anschriften_Primary] @oldRecId integer AS EXECUTE [dbo].[up_Next_Anschriften_Primary] @oldRecId')
GO

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Prior_Anschriften_Primary')
  DROP PROCEDURE [dbo].[up_Prior_Anschriften_Primary]
GO
CREATE PROCEDURE [dbo].[up_Prior_Anschriften_Primary] @oldRecId integer AS
  DECLARE @oldNr char(2)
  SELECT @oldNr = Nr FROM [dbo].[ut_Anschriften] WHERE RecId = @oldRecId

  DECLARE @newRecId integer

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

  SELECT TOP 1 @newRecId = ISNULL(RecId,0) FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary)
    WHERE (Nr < @oldNr)
    ORDER BY Nr 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_Anschriften_Primary')
  EXEC('ALTER PROCEDURE [dbo].[qp_Prior_Anschriften_Primary] @oldRecId integer AS EXECUTE [dbo].[up_Prior_Anschriften_Primary] @oldRecId')
GO

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

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

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

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

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary) WHERE 
      Nr = @mNr
      ORDER BY Nr,RecId
    RETURN
  END

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

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary) WHERE 
      Nr >= @mNr AND SUBSTRING(Nr,1,LEN(@mNr)) = @mNr
      ORDER BY Nr,RecId
    RETURN
  END

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

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

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

  IF @xLen = 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary) WHERE 
      Nr = @mNr
      ORDER BY Nr desc,RecId desc
    RETURN
  END

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

  IF @xLen < 2 BEGIN
    SELECT TOP 1 RecId FROM [dbo].[ut_Anschriften] WITH(INDEX = Ik_Primary) WHERE 
      SUBSTRING(Nr,1,LEN(@mNr)) = @mNr
      ORDER BY Nr desc,RecId desc
    RETURN
  END

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


