Print ''
Print '----------------------------------------------------'
Print ' JFTSql_20_PublicProceduresTSQL-TOOLS-JF Scripts    '
Print ' FirstRevision: 19.03.2006'
Print ' CurrRevision : 13.02.2007'
Print ' Autor        : J.Feuerstein'
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_in nvarchar(max), @DateTime_in datetime
As
BEGIN
  PRINT @Mess1_in + ' duration:[' + convert(varchar(4),DateDiff(ss, @DateTime_in, GetDate())) + ' sec][' + convert(varchar(4),DateDiff(ms, @DateTime_in, GetDate())) + ' ms]'
  -- PRINT @Mess1_in + ' duration:[' +
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 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 INDICES to delete')
  end

 --- 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 CONSTAINS to delete')
  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)
 -- print('-----------------------------------------------------------------')

  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_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


--      ALTER TABLE [dbo].[Objects_tbl]
--        ADD CONSTRAINT [Objects_tbl_pk]
--        -- PRIMARY KEY NONCLUSTERED ([Id])
--        -- PRIMARY KEY CLUSTERED ([Id])  angepasst 01.02.2011 07:41
--        PRIMARY KEY CLUSTERED (Id ASC)
--        WITH (
--          FILLFACTOR = 80,
--          PAD_INDEX = OFF,
--          STATISTICS_NORECOMPUTE = OFF,
--          ALLOW_ROW_LOCKS = ON,
--          ALLOW_PAGE_LOCKS = ON)

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

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 ''
Print '-------------------------------------------------------------------------'
Print ' JFTSql_20_PublicProcedures.sql Scripts Imported'
Print '-------------------------------------------------------------------------'
Print ''
GO

