Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Autor         : J.Feuerstein'
Print ' Creation Date : 02.02.2015'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


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


Create Procedure DS_IR_Relation_CHECK
                                    @OwningSiteID                nvarchar(64) = null,
                                    @Import_Obj_ObjectName       nvarchar(128) = null,
                                    @Import_Obj_ObjectType       nvarchar(64) = null,
                                    @ObjectCLINameNew            nvarchar(128) = null,
                                    @Import_Obj_IR               nvarchar(128) = null,
                                    @IrCLICur                    nvarchar(128) = null,
                                    @IrCLINew                    nvarchar(128) = null,
                                    @IrId_r                      uniqueidentifier = null Output

/* 02.02.2015 21:11
Repairs for Named Base Object IR and DS Relations */
As
BEGIN

 DECLARE @DataSetId_r uniqueidentifier
 DECLARE @DataSet_Ir_RelId uniqueidentifier
 DECLARE @DataSet_ItemIr_RelId uniqueidentifier

 DECLARE @DebugPrintOn bit
 set @DebugPrintOn = 0
 DECLARE @MyProcName nvarchar(32)
 set @MyProcName='DS_IR_Relation_CHECK'

 if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '----------------------' , '----------------------'
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '01-@@OwningSiteID' ,          @OwningSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '02-@Import_Obj_ObjectName' ,  @Import_Obj_ObjectName
  end


declare @DSId  uniqueidentifier,
        @IrCLICurId  uniqueidentifier,
        @IrCLINewId  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @ObjectFromSiteID nvarchar(32),
        @ProcessPacketName nvarchar(255)

 declare @IrCLICurTbl TABLE(
  IrCLICurId  uniqueidentifier not null,
  ObjectCliName nvarchar(255) not null
)
  DECLARE IrCLICurTbl_cursor CURSOR
  FOR SELECT IrCLICurId,ObjectCliName FROM @IrCLICurTbl
      ORDER BY ObjectCliName

  -- #Step: suche DS
   set @IrId_r = null
   select @DSId=Id from Objects_tbl WITH (nolock)
                 where ObjectName       = @Import_Obj_ObjectName
                   and ObjectFromSiteID = @OwningSiteID
                   and ObjectClass      = 'DS'
    if @DSId is NULL
    begin
       goto MyExit
    end

    -- #Step: suche IR aktuelle
    insert INTO @IrCLICurTbl
    select ID, ObjectCLIName
           from Objects_tbl WITH (nolock)
                where ObjectCLIName    = @IrCLICur
                  and ObjectFromSiteID = @OwningSiteID
                  and ObjectClass      = 'IR'

    OPEN IrCLICurTbl_cursor
    while 1=1
     begin
     FETCH NEXT FROM IrCLICurTbl_cursor into @IrCLICurId
     if (@@fetch_status <> 0) break
     -- Use Order by
     -- finde ersten
     --
     end
    CLOSE IrCLICurTbl_cursor
    DEALLOCATE IrCLICurTbl_cursor



    set @IrCLICurId=null
    select @IrCLICurId=Id from Objects_tbl WITH (nolock)
            where ObjectCLIName    = @IrCLICur
              and ObjectFromSiteID = @OwningSiteID
              and ObjectClass      = 'IR'

    if @IrCLICurId is NULL
    begin
       goto MyExit
    end

    -- #Step: suche IR aktuelle
    set @IrCLINewId=null
    select @IrCLINewId=Id from Objects_tbl WITH (nolock)
            where ObjectCLIName    = @IrCLINew
              and ObjectFromSiteID = @OwningSiteID
              and ObjectClass      = 'IR'

    if @IrCLICurId is NULL
    begin
       goto MyExit
    end

    if @IrCLINewId is NULL
    begin
       goto MyExit
    end

    -- #Step: Update DS
    UPDATE Objects_tbl SET [ObjectCLIName]=@ObjectCLINameNew where [Id]= @DSId


    -- #Step: remap DS - IR
    --   UPDATE Objects_Ir_Rel_tbl SET [IrId]='{0}' where [ObjectId]='{1}'



    -- #Step: Clean -    IR


 MyExit:

END
GO



ende:

Print ''
Print '-------------------------------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '-------------------------------------------------------------------------'

GO

