I've been using CodeProject as a research source for a number of years now. I figured it was about time that I gave back to the development community with a contribution of my own. I haven't seen anyone else take this approach to passing arrays before, so I hope my first article is not in vain.
Anyone that has worked with SQL Server for a while undoubtedly has come across a scenario where she/he needed a way to pass an array as a parameter to a stored procedure. One of the most common of these scenarios involves a stored procedure that must be executed passing numerous primary keys, which are typically integer-based. Currently, it is not possible in T-SQL to pass a traditional array from an external programming environment as a parameter to a stored procedure or to a user-defined function. One approach to overcome this is to build a SQL statement on the client-side with all of the literal values supplied. Unfortunately, this type of approach is often not scalable and prone to injection attacks. I’ve also seen other approaches in T-SQL, all of which involved creating a delimited string and splitting it up on the server side. Although that does work, there is a far more reusable and efficient way to perform this task using a little T-SQL magic provided that the parameters are integer-based.
An array is nothing more than a block of contiguous allocated memory. In the case of an array of strings, it is actually an array of arrays, where each string is an array of characters. Unfortunately there is no way to represent that type of data structure in T-SQL without using the dynamic SQL or delimited string approach. However, what about arrays with fixed-size memory elements such as integers? As it turns out, it is possible to represent this type of data in raw binary form using any of the following data types:
IMAGE. Since the
IMAGE data type has a 2GB maximum, and the largest segment extracted from this type of variable in this case is less than 8000 bytes, it makes the most appropriate choice.
Now that you know that an array could be represented in T-SQL, you’re probably wondering how you could access it or make use of it. As it turns out, the
SUBSTRING function is capable of working with binary data. Since slicing up a string is really nothing more than a little pointer manipulation, this makes perfect sense. We are able to use the
DATALENGTH function to determine the actual length of data passed and extract the parts of the binary into appropriately sized chunks where the pieces can be used in their integer form (
BIGINT respectively) without performing any data type casting. Of course, the size of the integer ultimately determines how many elements the array can actually have, but even a
BIGINT array could have somewhere in the neighborhood of 256,000 elements (2GB with 8 bytes per element), which should be more than enough.
A single stored procedure parameter can be easily separated into multiple pieces, but how can we use it like an array? The answer is a table-valued, user-defined function. Essentially, the binary parameter is passed to the function, divided into its respective pieces, and inserted into a memory table. Admittedly, this is very similar to the conventional delimited string approach except that it is much faster because no character searching or type casting takes place during the separation of values. The table that is returned contains two columns,
Value, which function just like an array. You may find that there is no need for an
Index column and you could remove it to further improve performance. I assume that there may be a case in which an element may need to be accessed by its ordinal position just like in a regular array. Without the
Index column, there would not be a way to do this unless you use a cursor.
To simplify the solution, I’ve created a single, resuable user-defined function called
udfIntegerArray, which accepts the array in binary form and the size of the array elements, in bytes (which must be 1, 2, 4, or 8 respectively).
Using the Code
The following demonstrates how to use the function in a stored procedure:
CREATE PROCEDURE dbo.uspProductsSearch
ProductID IN ( SELECT Value FROM dbo.udfIntegerArray( @ProductIDs, 4 ) );
You can simulate the parameter value using a literal binary value. The following demonstrates how to represent 1, 3, and 15 in literal binary form, where
Products.ProductID is a 32-bit integer:
DECLARE @ProductIDs VARBINARY(8000);
SET @ProductIDs = 0x00000001000000030000000F;
Points of Interest
Interestingly, I discovered that T-SQL supports equality of all intrinsic integer data types against their binary equivalents, which from a low-level perspective is completely palatable. This finding is important because that means only one user-defined function is needed to support all of the integer data types and explicit type casting is not required.
I've demonstrated how an array can be implemented on the database side, but how can an array be assigned to a parameter in binary form for a stored procedure? Surprisingly it isn’t very difficult to do in .NET, but to simplify the task I’ve created the
BinaryIntegerArrayFactory classes. The
BinaryIntegerArray class is a generic class that is a
List<T> of integers, where T is
System.UInt64. Since a type constraint cannot be applied in a way that would restrict the usable types to this list, the constructor is marked as internal and the
BinaryIntegerArrayFactory provides methods that will return an appropriate implementation of
BinaryIntegerArray class has all of the functionality of
List<T>, but adds the
ToHexadecimal() methods that return the contents of the list in the respective binary or hexadecimal format.
Keep in mind that although the
BinaryIntegerArray class supports unsigned integers, SQL Server does not. Using any of these implementations to supply an array to SQL Server can result in exceptions or unintended results.
Putting It All Together
The following is a snippet from the example code that illustrates how to create an array of integers and assign the binary equivalent value to the parameter of a stored procedure:
string cs = "Server=localhost;Database=Northwind;Integrated Security=SSPI";
BinaryIntegerArray<int> ids = BinaryIntegerArrayFactory.CreateInt32();
ids.Add( 1 );
ids.Add( 3 );
ids.Add( 15 );
using ( SqlConnection connection = new SqlConnection( cs ) )
using ( SqlCommand command = new SqlCommand( "dbo.uspProductsSearch", connection ) )
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = command.Parameters.Add( "@ProductIDs", SqlDbType.Binary );
param.Value = ids.ToBinary();
SqlDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection );
while ( reader.Read() )
Unfortunately, traditional arrays in T-SQL will be a long time coming, if ever. Hopefully, I have clearly demonstrated that although traditional methodologies cannot be used, it is possible to pass an array in binary form if the elements are in fixed-memory segments.
Although it is extra work, I do feel it is important that multi-language examples are key to reaching a broader audience. Therefore, the source code and examples provided have both C# and VB.NET implementations.
Replaced implementation of
IMAGE. Only 8000 bytes can be extracted from the
IMAGE data type at a time without using the
TEXTPTR functions. Since none of the elements are remotely near this size, the
IMAGE data type provides a more flexible implementation. The corresponding source SQL scripts were updated.
Chris is a senior software engineer working for a consulting firm in Sacramento, CA.
He enjoys working with object relational mapping (ORM) frameworks and the everyday challenges of software engineering.