Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@SQLKiwi
Created May 30, 2019 15:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f to your computer and use it in GitHub Desktop.
Save SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f to your computer and use it in GitHub Desktop.
using Microsoft.SqlServer.Server;
using System.Collections.Specialized;
using System.Data.SqlTypes;
public partial class UserDefinedFunctions
{
[return: SqlFacet(MaxSize = 132, IsNullable = false, IsFixedLength = true)]
[SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
public static byte[] NullableIntsToBinary
(
SqlInt32 Col01,
SqlInt32 Col02,
SqlInt32 Col03,
SqlInt32 Col04,
SqlInt32 Col05,
SqlInt32 Col06,
SqlInt32 Col07,
SqlInt32 Col08,
SqlInt32 Col09,
SqlInt32 Col10,
SqlInt32 Col11,
SqlInt32 Col12,
SqlInt32 Col13,
SqlInt32 Col14,
SqlInt32 Col15,
SqlInt32 Col16,
SqlInt32 Col17,
SqlInt32 Col18,
SqlInt32 Col19,
SqlInt32 Col20,
SqlInt32 Col21,
SqlInt32 Col22,
SqlInt32 Col23,
SqlInt32 Col24,
SqlInt32 Col25,
SqlInt32 Col26,
SqlInt32 Col27,
SqlInt32 Col28,
SqlInt32 Col29,
SqlInt32 Col30,
SqlInt32 Col31,
SqlInt32 Col32
)
{
// The byte array to return (initialized to zeroes)
byte[] buffer = new byte[132];
// NULL bitmap (initially all bits unset)
BitVector32 bitmap = new BitVector32(0);
unsafe
{
// Make sure the byte array doesn't move
fixed (byte* ptr = buffer)
{
// Access as integer pointers
int* iptr = (int*)ptr;
// For each input column:
// If not NULL copy the integer value into the buffer at the right point
// Else set the corresponding null bitmap bit
if (!Col01.IsNull) { iptr[00] = Col01.Value; } else { bitmap[1 << 00] = true; }
if (!Col02.IsNull) { iptr[01] = Col02.Value; } else { bitmap[1 << 01] = true; }
if (!Col03.IsNull) { iptr[02] = Col03.Value; } else { bitmap[1 << 02] = true; }
if (!Col04.IsNull) { iptr[03] = Col04.Value; } else { bitmap[1 << 03] = true; }
if (!Col05.IsNull) { iptr[04] = Col05.Value; } else { bitmap[1 << 04] = true; }
if (!Col06.IsNull) { iptr[05] = Col06.Value; } else { bitmap[1 << 05] = true; }
if (!Col07.IsNull) { iptr[06] = Col07.Value; } else { bitmap[1 << 06] = true; }
if (!Col08.IsNull) { iptr[07] = Col08.Value; } else { bitmap[1 << 07] = true; }
if (!Col09.IsNull) { iptr[08] = Col09.Value; } else { bitmap[1 << 08] = true; }
if (!Col10.IsNull) { iptr[09] = Col10.Value; } else { bitmap[1 << 09] = true; }
if (!Col11.IsNull) { iptr[10] = Col11.Value; } else { bitmap[1 << 10] = true; }
if (!Col12.IsNull) { iptr[11] = Col12.Value; } else { bitmap[1 << 11] = true; }
if (!Col13.IsNull) { iptr[12] = Col13.Value; } else { bitmap[1 << 12] = true; }
if (!Col14.IsNull) { iptr[13] = Col14.Value; } else { bitmap[1 << 13] = true; }
if (!Col15.IsNull) { iptr[14] = Col15.Value; } else { bitmap[1 << 14] = true; }
if (!Col16.IsNull) { iptr[15] = Col16.Value; } else { bitmap[1 << 15] = true; }
if (!Col17.IsNull) { iptr[16] = Col17.Value; } else { bitmap[1 << 16] = true; }
if (!Col18.IsNull) { iptr[17] = Col18.Value; } else { bitmap[1 << 17] = true; }
if (!Col19.IsNull) { iptr[18] = Col19.Value; } else { bitmap[1 << 18] = true; }
if (!Col20.IsNull) { iptr[19] = Col20.Value; } else { bitmap[1 << 19] = true; }
if (!Col21.IsNull) { iptr[20] = Col21.Value; } else { bitmap[1 << 20] = true; }
if (!Col22.IsNull) { iptr[21] = Col22.Value; } else { bitmap[1 << 21] = true; }
if (!Col23.IsNull) { iptr[22] = Col23.Value; } else { bitmap[1 << 22] = true; }
if (!Col24.IsNull) { iptr[23] = Col24.Value; } else { bitmap[1 << 23] = true; }
if (!Col25.IsNull) { iptr[24] = Col25.Value; } else { bitmap[1 << 24] = true; }
if (!Col26.IsNull) { iptr[25] = Col26.Value; } else { bitmap[1 << 25] = true; }
if (!Col27.IsNull) { iptr[26] = Col27.Value; } else { bitmap[1 << 26] = true; }
if (!Col28.IsNull) { iptr[27] = Col28.Value; } else { bitmap[1 << 27] = true; }
if (!Col29.IsNull) { iptr[28] = Col29.Value; } else { bitmap[1 << 28] = true; }
if (!Col30.IsNull) { iptr[29] = Col30.Value; } else { bitmap[1 << 29] = true; }
if (!Col31.IsNull) { iptr[30] = Col31.Value; } else { bitmap[1 << 30] = true; }
if (!Col32.IsNull) { iptr[31] = Col32.Value; } else { bitmap[1 << 31] = true; }
// Write completed NULL bitmap into the buffer
iptr[32] = bitmap.Data;
}
}
return buffer;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment