using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace getUids1C
{
    public static class CLR_Func
    {
        [SqlFunction(FillRowMethodName = "getUid_getRow")]
        public static IEnumerable getUid(byte[] c1, byte[] c2, byte[] c3, byte[] c4)
        {
            return new UidsRecord[] { new UidsRecord(c1, c2, c3, c4) };
        }


        public static void getUid_getRow(object obj, out SqlGuid c1, out SqlGuid c2, out SqlGuid c3, out SqlGuid c4)
        {
            UidsRecord o = (UidsRecord)obj;
            c1 = new SqlGuid(o.c1);
            c2 = new SqlGuid(o.c2);
            c3 = new SqlGuid(o.c3);
            c4 = new SqlGuid(o.c4);
        }


        public class UidsRecord
        {
            public byte[] c1;
            public byte[] c2;
            public byte[] c3;
            public byte[] c4;

            public UidsRecord(byte[] a1, byte[] a2, byte[] a3, byte[] a4)
            {
                formatBytes(a1, out c1);
                formatBytes(a2, out c2);
                formatBytes(a3, out c3);
                formatBytes(a4, out c4);
            }

            private void formatBytes(byte[] buffer1, out byte[] buffer2)
            {
                buffer2 = new byte[] {
                                   buffer1[15],
                                   buffer1[14],
                                   buffer1[13],
                                   buffer1[12],
                                   buffer1[11],
                                   buffer1[10],
                                   buffer1[9],
                                   buffer1[8],
                                   buffer1[0],
                                   buffer1[1],
                                   buffer1[2],
                                   buffer1[3],
                                   buffer1[4],
                                   buffer1[5],
                                   buffer1[6],
                                   buffer1[7]
            };
            }
        }

    }
}

create assembly getUids1C from 'C:\source\getUids1C\getUids1C.dll';
go
create function fn_getUid (
@c1 varbinary(16),
@c2 varbinary(16),
@c3 varbinary(16),
@c4 varbinary(16)
)
returns table (c1 uniqueidentifier, c2 uniqueidentifier, c3 uniqueidentifier, c4 uniqueidentifier)
as
external name [getUids1C].[getUids1C.CLR_Func].[getUid];
go

declare
       @c1 uniqueidentifier = newid(),
       @c2 uniqueidentifier = newid(),
       @c3 uniqueidentifier = newid(),
       @c4 uniqueidentifier = 'C11C7981-EADE-4343-11EB-C3D76298209B';

declare
       @b1 varbinary(16) = cast(@c1 as varbinary(16)),
       @b2 varbinary(16) = cast(@c2 as varbinary(16)),
       @b3 varbinary(16) = cast(@c3 as varbinary(16)),
       @b4 varbinary(16) = cast(@c4 as varbinary(16))

select @c1 as [c1], @c2 as [c3], @c3 as [c3], @c4 as [c4];
select * from [dbo].[fn_getUid](@b1, @b2, @b3, @b4);

select top 1000
				x.c1 as fk_agr,
				x.c2 as fk_obj,
				x.c3 as fk_pgm,
				x.c4 as fk_rsk,
				ods.dbo.fn_bin2guid(_Fld7885RRef) as fk_ins,
				ods.dbo.fn_bin2guid(_RecorderRRef) as fk_reg,
				ods.dbo.fn_bin2guid(_Fld7885RRef) as fk_cnt
				into #tmp1
	from STG.._InfoRg7879 t cross apply [dbo].[fn_getUid] (_Fld7880RRef, _Fld7881RRef, _Fld7882RRef, _Fld7883RRef) x