Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database...........: #DB_N#'
Print ' Executing SQL Script.....: JFTSql_20_PublicProcedures.sql Scripts'
Print ' #New.....................: 19.03.2006 / J.Fes'
Print ' #LUp.....................: 30.07.2024 / J.Fes Optim:JF_ShowTimeDiffToNow'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

-- Declares a user defined data type: A table with a single indexed column of GUIDs
Print '-- [GUID_TABLE DataType] ------------------------------------------------'
IF TYPE_ID(N'GUID_TABLE') IS NULL
BEGIN
  CREATE TYPE GUID_TABLE AS TABLE (Id uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED)
END
GO


Print '-- [JF_ShowTimeDiffToNow] -----------------------------------------------'
If Exists (select Name from sysobjects  where Name = "JF_ShowTimeDiffToNow" and type = "P") Drop Procedure JF_ShowTimeDiffToNow
GO
Create Procedure JF_ShowTimeDiffToNow @Mess1_p nvarchar(max), @DateTimeStart_p datetime, @DateTimeEnd_p datetime = null
As
BEGIN
  -- #LUp 23.05.2024/J.Fes
  -- #LUp 30.07.2024/J.Fes add paramter @DateTimeEnd_p

  DECLARE @start datetime, @end datetime, @Txt_r nvarchar(max)
  if @DateTimeEnd_p IS NOT NULL
  begin
    set @end = @DateTimeEnd_p
  end
  else
  begin
   set @end = GetDate()
  end

  set @start = @DateTimeStart_p

  if @end < @start
  begin
     DECLARE @DateTmp datetime
     set @DateTmp = @start
     set @start = @end
     set @end = @DateTmp
  end


set @Txt_r = Convert(varchar, DateDiff(dd, @start, @end)) + '(d):'
           + Convert(varchar, DateDiff(hh, @start, @end) % 24) + '(h):'
    						 + Convert(varchar, DateDiff(mi, @start, @end) % 60) + '(m):'
    						 + Convert(varchar, DateDiff(ss, @start, @end) % 60) + '(s):'
    						-- + Convert(varchar, DateDiff(ms, @start, @end) % 100) + '(ms):'

 PRINT (@Mess1_p + ' Start-DT: ' + CONVERT(varchar,@start,120))
 PRINT (@Mess1_p + ' End-DT..: ' + CONVERT(varchar,@end,120))
 PRINT (@Mess1_p + ' Diff-DT.: ' + @Txt_r)

END
GO

--------------------------------------------------------------------------------

Print '-- [JF_DropIndicesOfTable] ----------------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_DropIndicesOfTable" and type = "P") Drop Procedure JF_DropIndicesOfTable
GO
Create Procedure JF_DropIndicesOfTable @TableName nvarchar(max)
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)
 print('------------------------------------------------------------------------')
 print('delete all indices and constraint of table:[' + @TableName + ']')

 --- Step: löschen aller CONSTRAINT's ------------------------------------------
 set @ExecuteSQLstr=null
 select @ExecuteSQLstr=isnull(@ExecuteSQLstr + ';','')
                       + 'ALTER TABLE [dbo].[' + @TableName + ']'
                       + ' DROP CONSTRAINT ['+ [name] + ']'
 from sys.indexes
 WHERE [object_id] = OBJECT_ID(@TableName) and ([is_unique]=1 or [is_primary_key]=1)

 if not @ExecuteSQLstr=''
  begin
    print (' - Delete CONSTRAINT via:')
    print (@ExecuteSQLstr)
    exec  (@ExecuteSQLstr)
  end
  else
  begin
    print (' - NO CONSTRAINT to delete')
  end


 --- Step: löschen aller indices -----------------------------------------------
 set @ExecuteSQLstr=null
 select @ExecuteSQLstr=isnull(@ExecuteSQLstr + ';','')
                      + 'DROP INDEX ['+ name + '] ON ' + object_name(object_id)
 from sys.indexes
 WHERE [object_id] = OBJECT_ID(@TableName) and ([is_unique]=0 and [is_primary_key]=0)

 if not @ExecuteSQLstr=''
  begin
    print (' - Delete INDICES via:')
    print (@ExecuteSQLstr)
    exec  (@ExecuteSQLstr)
  end
  else
  begin
    print (' - NO INDEX to delete')
  end
END
GO
--------------------------------------------------------------------------------
Print '-- [JF_DropDataBaseAllConstraints] #New 18.10.2020/J.Fes ------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_DropDataBaseAllConstraints" and type = "P") Drop Procedure JF_DropDataBaseAllConstraints
GO
Create Procedure JF_DropDataBaseAllConstraints

As
BEGIN

 DECLARE @ExecuteSQLstr nvarchar(MAX)
 DECLARE @DatabaseName nvarchar(MAX)

 SET @ExecuteSQLstr = N''

 -- SELECT @DatabaseName=DB_NAME()

 print('------------------------------------------------------------------------')
 print('DROP all FOREIGN KEY of Database:[' + DB_NAME() + ']')

 -- 18.10.2020 Source https://stackoverflow.com/questions/12721823/how-to-drop-all-foreign-key-constraints-in-all-tables


 SELECT @ExecuteSQLstr = @ExecuteSQLstr + N'ALTER TABLE ' + QUOTENAME(KCU1.TABLE_SCHEMA)
     + N'.' + QUOTENAME(KCU1.TABLE_NAME)
     + N' DROP CONSTRAINT ' -- + QUOTENAME(rc.CONSTRAINT_SCHEMA)  + N'.'  -- not in MS-SQL
     + QUOTENAME(RC.CONSTRAINT_NAME) + N'; ' + CHAR(13) + CHAR(10)
 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
     ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
     AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
     AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

 if not @ExecuteSQLstr=''
  begin
   Print ('DROP FOREIGN KEY: now via:')
   Print (@ExecuteSQLstr)
   exec  (@ExecuteSQLstr)
  end
  else
  begin
   Print ('DROP FOREIGN KEY: NOT done because NO FOREIGN KEY found')
  end

END
GO

--------------------------------------------------------------------------------

Print '-- [JF_IndexCheckCreateDef] ---------------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_IndexCheckCreateDef" and type = "P") Drop Procedure JF_IndexCheckCreateDef
GO
Create Procedure JF_IndexCheckCreateDef (
      @IndexName    nvarchar(max) ,
      @TableName    nvarchar(max) ,
      @ColName      nvarchar(max) ,
      @INCLUDECol   nvarchar(max) = NULL
     )
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)

  If NOT Exists (select Name from sysindexes where Name = @IndexName)
  begin
    -- 11.06.2015 09:56 -->  Oti + CREATE INDEX IndexName ON Tabelle (IndexFeld1, IndexFeld2) INCLUDE (IncludeFeld1, IncludeFGeld2)
     set @ExecuteSQLstr='CREATE NONCLUSTERED INDEX [' + @IndexName  + '] ON [dbo].[' + @TableName + '] (' + @ColName + ')' +
                  ISNULL(' INCLUDE (' + @INCLUDECol + ') ', '') +
                        ' WITH (FILLFACTOR = 80) ON [PRIMARY]'
     Print ('    |-> Index.Create: tbl:[' + @TableName + '] Col:[' + @ColName + '] Index:[' + @IndexName + ']' )
     Print ('    |-> via:' + @ExecuteSQLstr )
     exec (@ExecuteSQLstr)
  end
  else
  begin
     Print ('    |-> Index.Check:[OK] tbl:[' + @TableName + '] Col:[' + @ColName +   '] Index:[' + @IndexName + ']' )
  end

END
GO
--------------------------------------------------------------------------------
Print '-- [JF_IndexDelete] -----------------------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_IndexDelete" and type = "P") Drop Procedure JF_IndexDelete
GO
Create Procedure JF_IndexDelete (
      @IndexName    nvarchar(max) ,
      @TableName    nvarchar(max) ,
      @ColName      nvarchar(max),
      @PrintVerbose nvarchar(max) = 'NO'
     )
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)

  If Exists (select Name from sysindexes where Name = @IndexName)
  begin
     set @ExecuteSQLstr='DROP INDEX [' + @IndexName + '] ON [dbo].[' + @TableName + ']'
     Print ('    |-> Delete Index:[' + @IndexName + '] tbl:[' + @TableName + '] of Colume[s]:' + @ColName)
     exec (@ExecuteSQLstr)
     If not Exists (select Name from sysindexes where Name = @IndexName)
     begin
        Print ('    |-> Delete Index Result:[OK]')
     end
     else
     begin
        Print ('    |-> Delete Index Result:[ERR]:[' + @@ERROR + ']')
     end
  end
  else
  begin
      if NOT @PrintVerbose = 'NO'
      begin
       Print ('    |-> Index:[' + @IndexName + '] did not Exist nothing is to be DONE')
      end
  end

END
GO
--------------------------------------------------------------------------------
Print '-- [JF_ColumeRename #New 07.03.2020/J.Fes] ------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_ColumeRename" and type = "P") Drop Procedure JF_ColumeRename
GO
Create Procedure JF_ColumeRename (
      @TableName_p     nvarchar(max),
      @ColumeNameOld_p nvarchar(max),
      @ColumeNameNew_p nvarchar(max),
      @PrintVerbose_p  nvarchar(max) = 'NO'
     )
As
-- #Doc: Procedure rename for tbale columes
-- #Doc: This Procedure uses checks/conditions
-- If Colum with Old Name exists --> if not nothing happened
-- If OLD and NEW Colum exist --> if Yes then Conflict ERROR is printed
-- If New Colum exist then --> Nothing happened because new column do exist
-- Renaming is case sensitive --> It renames: --> #Exa: Mycolume -> MyColume
-- #Exa: EXECUTE JF_ColumeRename 'TableName',  'ColumNameOld', 'ColumNameNew'
-- #Exa: EXECUTE JF_ColumeRename @MyTableName,  'NativeBomReadFromCache', 'NativeBOMReadFromCache'


BEGIN
  declare @ExecuteStr nvarchar(max)
  declare @ColumeNameOld_found nvarchar(max)
  declare @ColumeNameNew_found nvarchar(max)

  -- #Doc: search for Old Colume Name
  SELECT @ColumeNameOld_found = NAME FROM sys.columns WHERE name = @ColumeNameOld_p AND OBJECT_NAME(object_id) = @TableName_p
--   SELECT @ColumeNameOld_found = COLUMN_NAME
--       FROM INFORMATION_SCHEMA.COLUMNS
--       WHERE TABLE_NAME = @TableName_p
--             and COLUMN_NAME = @ColumeNameOld_p

  -- #Doc: search for NEW Colume Name
  SELECT @ColumeNameNew_found = NAME FROM sys.columns WHERE name = @ColumeNameNew_p AND OBJECT_NAME(object_id) = @TableName_p

--   SELECT @ColumeNameNew_found = COLUMN_NAME
--       FROM INFORMATION_SCHEMA.COLUMNS
--       WHERE TABLE_NAME = @TableName_p
--             and COLUMN_NAME = @ColumeNameNew_p

--  print ('!!! - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - !!!')

--   print(@ColumeNameOld_p)
--   print(@ColumeNameNew_p)
--   print(@ColumeNameOld_found)
--   print(@ColumeNameNew_found)
  if @ColumeNameOld_found IS NOT NULL and (convert(varbinary(max),@ColumeNameOld_found)<>convert(varbinary(max),@ColumeNameNew_p))
    BEGIN
      print ('!! -> Action rename Colume:' + @TableName_p + '.' + @ColumeNameOld_found + ' -> to:' + @ColumeNameNew_p)
      if (convert(varbinary(max),@ColumeNameNew_found)=convert(varbinary(max),@ColumeNameNew_p))
       BEGIN
         print('ERROR: rename conflict!')
         print('Because colume to rename.....:' + @ColumeNameOld_p + ' exist')
         print('and also colume with new Name:' + @ColumeNameNew_p + ' exist')
         print('please inform................: Application vendor')
       END
       ELSE
       BEGIN
         set @ExecuteStr='sp_rename ' + 'N''' + @TableName_p + '.' + @ColumeNameOld_found + '''' + ', ' + 'N''' + @ColumeNameNew_p + '''' + ', ' + '''' + 'COLUMN' + ''''
         print ('execute ' + @ExecuteStr)
         -- BEGIN TRANSACTION
         execute (@ExecuteStr)
         -- COMMIT TRANSACTION
       END
     END

     -- #Doc: New coume exist
--      if @PrintVerbose_p <> 'NO'
--       BEGIN
--         -- #Doc: search for NEW Colume Name
--         SELECT @ColumeNameNew_found = COLUMN_NAME
--             FROM INFORMATION_SCHEMA.COLUMNS
--             WHERE TABLE_NAME = @TableName_p
--                   and COLUMN_NAME = @ColumeNameNew_p
--          if @ColumeNameNew_found IS NOT NULL
--          BEGIN
--            print ('rename Colume: OK because: Colume:' + @TableName_p + '.' + @ColumeNameNew_p + ' exist')
--          END
--       END
END



-- GO
  -- #Debug:
--   CREATE TABLE [dbo].[TESTDellMe_tbl] (
--         [Colume1]   [nvarchar] (255) NULL ,
--         [Colume2]   [nvarchar] (255) NULL ,
--     ) ON [PRIMARY]
--   EXECUTE JF_ColumeRename 'TESTDellMe_tbl',  'Colume1', 'Colume3', 'YES'
--   EXECUTE JF_ColumeRename 'TESTDellMe_tbl',  'Colume3', 'Colume4', 'YES'
--   EXECUTE JF_ColumeRename 'TESTDellMe_tbl',  'Colume4', 'Colume1', 'YES'
--   EXECUTE JF_ColumeRename 'TESTDellMe_tbl',  'Colume4', 'Colume1', 'YES'
--   print ('!!! - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - !!!')
GO
--------------------------------------------------------------------------------


Print '-- [JF_PrimKeyCheckCreateDef] -------------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_PrimKeyCheckCreateDef" and type = "P") Drop Procedure JF_PrimKeyCheckCreateDef
GO
Create Procedure JF_PrimKeyCheckCreateDef (
      @PrimKeyName  nvarchar(max) ,
      @TableName    nvarchar(max) ,
      @ColName  nvarchar(max)
     )
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)
 -- print('-----------------------------------------------------------------')

--  If NOT Exists (select Name from sysindexes where Name = @IndexName)
  If NOT Exists (select Name from sysindexes where Name = @PrimKeyName)
  begin
     set @ExecuteSQLstr='ALTER TABLE [dbo].' + @TableName +
                        ' ADD CONSTRAINT [' + @PrimKeyName  + ']' +
                        ' PRIMARY KEY CLUSTERED (' + @ColName + ' ASC)' +
                        ' WITH (' +
                        ' FILLFACTOR = 80,' +
                        ' PAD_INDEX = OFF,' +
                        ' STATISTICS_NORECOMPUTE = OFF,' +
                        ' ALLOW_ROW_LOCKS = ON,' +
                        ' ALLOW_PAGE_LOCKS = ON)'
     Print ('PrimK.Create:[New] tbl:[' + @TableName + '] Col:[' + @ColName +   '] Index:[' + @PrimKeyName + ']' )
     Print ('|-> [via:' + @ExecuteSQLstr + ']')
     exec (@ExecuteSQLstr)
  end
  else
  begin
     Print ('    |-> PrimK.Check:[OK] tbl:[' + @TableName + '] Col:[' + @ColName + '] Index:[' + @PrimKeyName + ']' )
  end

END
GO


--------------------------------------------------------------------------------

Print '-- [zzzzzDebug_tbl] -----------------------------------------------------'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzzzzDebug_tbl]')
  and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[zzzzzDebug_tbl]
GO

-- erstellt die Tabelle: zzzzzDebug_tbl
CREATE TABLE [dbo].[zzzzzDebug_tbl] (
       [ProcGroup]         nvarchar(max)  , -- = Gruppenname
       [Name]              nvarchar(max) , -- = Name Debug wert
       [Value]             nvarchar(max) , -- = Wert
       [DateTimeStamp]     datetime NULL
)
ON [PRIMARY]
GO


Print '-- [zzzzzDebugTbl_AddDataSet] -------------------------------------------'
If Exists (select Name from sysobjects where Name = "zzzzzDebugTbl_AddDataSet" and type = "P") Drop Procedure zzzzzDebugTbl_AddDataSet
GO

Create Procedure zzzzzDebugTbl_AddDataSet @ProcGroup   nvarchar(max),
                                          @Name        nvarchar(max),
                                          @Value       nvarchar(max)
/* Lastet die Debug infos in die in die Datenbank ein */
As
BEGIN
     -- set @Value=@Value + N'<END>'
     INSERT INTO zzzzzDebug_tbl (ProcGroup , Name ,   Value  , DateTimeStamp )
                         VALUES (@ProcGroup , @Name , @Value , GetDate() )
     Return 1
END
GO



Print '-- [zzzzzDebugTbl_Empty] ------------------------------------------------'
If Exists (select Name from sysobjects where Name = "zzzzzDebugTbl_Empty" and type = "P") Drop Procedure zzzzzDebugTbl_Empty
GO

Create Procedure zzzzzDebugTbl_Empty
/* Lastet die Debug infos in die in die Datenbank ein   */
As
BEGIN
     Delete from dbo.zzzzzDebug_tbl
     Return 1
END
GO


-- Beispiel für erstellung eines Datensatzes
-- EXECUTE zzzzzDebugTbl_AddDataSet 'TestProcGroup' , 'TestName' , 'TestValue'


Print '-- [JF_DateResetCheck] --------------------------------------------------'
If Exists (select Name from sysobjects  where Name = "JF_DateResetCheck" and type = "P") Drop Procedure JF_DateResetCheck
GO

Create Procedure JF_DateResetCheck @DateResetCheckIn datetime Output
-- new 9.12.2008 J.Fes
-- check for JF_DateReset
-- Funktion: @DateResetCheckIn wird = NULL wenn @DateResetCheckIn das Datum = 01.01.1900 hat
As
BEGIN
 if @DateResetCheckIn = '19000101'
  BEGIN
   set @DateResetCheckIn = NULL
  END
END
GO




--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Print '-- [zzzzzDbMaintain_tbl] ------------------------------------------------'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzzzzDbMaintain_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto zzzzzDbMaintain_tbl_TableUpdate
  END
ELSE
  BEGIN
   goto zzzzzDbMaintain_tbl_TableCreate
  END

zzzzzDbMaintain_tbl_TableCreate:

-- erstellt die Tabelle: zzzzzDbMaintain_tbl
CREATE TABLE [dbo].[zzzzzDbMaintain_tbl] (
       [Id]                uniqueidentifier NOT NULL,
       [Name]              nvarchar(64) , -- Name
       [Value]             nvarchar(max) , -- Wert
       [DateTimeStamp]     datetime NULL   -- Zeitstempel an dem der Eintrag vorgenommen wurde
)
ON [PRIMARY]

goto zzzzzDbMaintain_tbl_TableUpdate

zzzzzDbMaintain_tbl_TableUpdate:
  EXECUTE JF_PrimKeyCheckCreateDef 'zzzzzDbMaintain_tbl_pk', 'zzzzzDbMaintain_tbl', 'Id'
  EXECUTE JF_IndexCheckCreateDef   'zzzzzDbMaintain_tbl_Idx01', 'zzzzzDbMaintain_tbl', 'Name'
GO



Print '-- [zzzzzDbMaintain_valueSave] ------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "zzzzzDbMaintain_valueSave" and type = "P")
Drop Procedure zzzzzDbMaintain_valueSave
GO


Create Procedure zzzzzDbMaintain_valueSave @Name             nvarchar(64) ,
                                           @Value            nvarchar(max)
-- sichert Name mit Value
As
BEGIN
  Declare @Id uniqueidentifier
  set @Id=Null
  select @Id=Id from zzzzzDbMaintain_tbl
         where Name = @Name

  if @Id IS NULL
     BEGIN
       SET @Id = NEWID()
       INSERT INTO zzzzzDbMaintain_tbl (ID ,  Name ,   Value , DateTimeStamp)
                               VALUES  (@Id,  @Name , @Value , GetDate())
     END
    ELSE
     BEGIN
      UPDATE zzzzzDbMaintain_tbl
            SET Name = @Name ,
                Value = @Value ,
                DateTimeStamp = GetDate()
            Where ID = @Id
     END
END
GO


Print '-- [zzzzzDbMaintain_valueGet] -------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "zzzzzDbMaintain_valueGet" and type = "P")
Drop Procedure zzzzzDbMaintain_valueGet
GO


Create Procedure zzzzzDbMaintain_valueGet
                                           @Name  nvarchar(64) ,
                                           @Value nvarchar(max) Output ,
                                           @ValueCompare nvarchar(max),
                                           @ValueCompareStat int Output
-- gibt das Value zu einem Namen zurück
As
BEGIN
  Declare @Id uniqueidentifier
  set @Value=Null
  set @Id=Null

  select @Id=Id, @Value=Value from zzzzzDbMaintain_tbl  where Name = @Name

  if @Id IS Not NULL
     BEGIN
       if @Value = @ValueCompare
          begin
           -- Value Exisiert und ist gleich
           set @ValueCompareStat = 1
          end
       ELSE
          begin
           -- Value Exisiert und ist ungleich
           set @ValueCompareStat = 2
          end
     END
    ELSE
     BEGIN
       -- Value Exisiert nicht
       set @ValueCompareStat = 3
     END
END
GO

Print '-- [SplitStringToTable_UDF] ---------------------------------------------'
If Exists (select Name from sysobjects
        where Name = 'SplitStringToTable_UDF'
        and type IN ('FN','IF')) Drop Function SplitStringToTable_UDF
GO

Create Function SplitStringToTable_UDF (@list  nvarchar(MAX),
                                        @delim nchar(1) = ',') RETURNS TABLE
-- new 25.08.2015 A.Becker
-- Funktion: Splittet eine String anhand eines Separators (single character) in eine Tabelle.
-- Returns: Table(Value, RowNumber)
As
RETURN
   WITH csvtbl(start, stop) AS (

     SELECT start = convert(bigint, 1),
            stop = charindex(@delim, @list + @delim)

     UNION ALL

     SELECT start = stop + 1,
            stop = charindex(@delim, @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0

   )
   SELECT substring(@list, start,
                    CASE WHEN stop > 0 THEN stop - start ELSE 0 END)
          AS Value, 
          ROW_NUMBER() OVER (ORDER BY @@rowcount) AS RowNumber
   FROM   csvtbl
   WHERE  stop > 0
GO



Print '-- [StringFormat_UDF] ---------------------------------------------------'
If Exists (select Name from sysobjects
             where Name = 'StringFormat_UDF'
             and type IN ('FN','IF')) Drop Function StringFormat_UDF
GO

Create Function StringFormat_UDF (
                                    @Format NVARCHAR(4000),
                                    @Parameters NVARCHAR(4000)
                                 ) RETURNS NVARCHAR(MAX)
-- new 25.08.2015 A.Becker
-- Funktion: Formatiert einen String mit Platzhaltern ähnlich wie .NET String.Format()
--           Die Argumente müssen in einem einzelnen String komma separiert übergeben werden
-- Sample Call: SELECT dbo.StringFormat( N'Format {0} {1} {2} {0}', N'1,2,3' )
-- Ergebgnis:   N'Format 1 2 3'
As
BEGIN

    DECLARE @Message NVARCHAR(400),
            @Delimiter CHAR(1)
    DECLARE @ParamTable TABLE ( ID INT IDENTITY(0,1), Paramter VARCHAR(1000) )
    
    SELECT @Message = @Format, @Delimiter = ','
    ;WITH CTE (StartPos, EndPos) AS
    (
      SELECT 1, CHARINDEX(@Delimiter, @Parameters)
      UNION ALL
      SELECT EndPos + (LEN(@Delimiter)), CHARINDEX(@Delimiter,@Parameters, EndPos + (LEN(@Delimiter)))
      FROM CTE
      WHERE EndPos > 0
    )
    INSERT INTO @ParamTable ( Paramter )
    SELECT
      [ID] = SUBSTRING ( @Parameters, StartPos, CASE WHEN EndPos > 0 THEN EndPos - StartPos ELSE 4000 END )
    FROM CTE
    UPDATE @ParamTable SET @Message = REPLACE ( @Message, '{'+CONVERT(VARCHAR,ID) + '}', Paramter )
    
    RETURN @Message

END
GO



Print '-- [StringTrim_UDF] -----------------------------------------------------'
If Exists (select Name from sysobjects
           where Name = 'StringTrim_UDF' and type IN ('FN','IF'))
           Drop Function StringTrim_UDF
GO

Create Function StringTrim_UDF (
                                 @string NVARCHAR(MAX)
                                )
                                 RETURNS NVARCHAR(MAX)
-- new 26.08.2015 A.Becker
-- Funktion: Trimmt einen Text vorne und hinten, gibt einen leeren Text statt NULL zurück
AS
BEGIN
  RETURN LTRIM(RTRIM(ISNULL(@string,'')))
END
GO



Print '-- [StringHasText_UDF] --------------------------------------------------'
If Exists (select Name from sysobjects
           where Name = 'StringHasText_UDF'
                 and type IN ('FN','IF')) Drop Function StringHasText_UDF
GO

Create Function StringHasText_UDF (
                                     @string NVARCHAR(MAX)
                                  )
                                      RETURNS BIT
-- new 26.08.2015 A.Becker
-- Funktion: Ermittelt, ob ein String Inhalt hat, d.h. weder NULL noch "" ist
AS
BEGIN

  DECLARE @res BIT

  IF (@string IS NULL) OR (LEN(@string) = 0) OR (@string = '')
    SET @res = 0
  ELSE
    SET @res = 1

  RETURN @res

END
GO

Print '-- [GETLOCALDATE] --------------------------------------------------'
If Exists (select Name from sysobjects
           where Name = 'GETLOCALDATE'
                 and type IN ('FN','IF')) Drop Function GETLOCALDATE
GO

CREATE FUNCTION [dbo].[GETLOCALDATE]()
RETURNS DATETIME
-- #New: 16.01.2025 J.Fes
-- #Source see: https://azureops.org/articles/get-local-date-in-azure-sql-database/#:~:text=The%20system%20time%20for%20Azure,create%20the%20below%20scaler%20function.
-- Funktion: Returns local time of sql server

AS
BEGIN
RETURN CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'FLE Standard Time' AS datetime)
END



-- execute JF_DropDataBaseAllConstraints
--  'Objects_ObjectGroups_Rel_tbl'
GO
Print ''
Print '-------------------------------------------------------------------------'
Print ' JFTSql_20_PublicProcedures.sql Scripts Imported'
Print '-------------------------------------------------------------------------'
Print ''
GO

