 | MSSQL Limitations and Arrays MSSQL is a favorite database platform for C# ASP .NET developers. For the most part, MSSQL has everything you need for your application. However, one particular missing feature is support for arrays as inputs to stored procedures. At first glance, one may think passing in a series of input parameters, with a pre-defined limit (such as var1, var, var3, var4) would be a suitable workaround. However, there is an easier way to accept an array of parameters. |
A First Glance Method for Accepting an Array CREATE PROCEDURE [dbo].[GetData]
@Var1 as varchar(50),
@Var2 as varchar(50),
@Var3 as varchar(50),
@Var4 as varchar(50),
@Var5 as varchar(50)
AS
BEGIN
SELECT * FROM DATA WHERE Code = @Var1 OR Code = @Var2 OR Code = @Var3 OR Code = @Var4 OR Code = @Var5
END The above idea not only limits the usefulness and flexibility of the stored procedure, it is also cumbersome to work with. By changing the series of var parameters into a single array parameter, we can process the data using SQL's IN command. Simple Method to Pass Array to a Stored Procedure - SQL Server Side Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command. CREATE PROCEDURE [dbo].[GetData]
@MyCodes as varchar(500) = '', -- comma delimited list of codes, ie: '''ABC'', ''DEF'', ''GHI'''
AS
BEGIN
DECLARE @query as nvarchar(500) set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'
exec SP_EXECUTESQL @query,
N'@p_MyCodes varchar(500)',
@p_MyCodes = @MyCodes
END The above stored procedure definition will accept a comma-delimited string, which we process as an array using the SQL IN command. Note, we had to use dyanmic SQL to properly form the query (which involves expanding the comma-delimited string). Simple Method to Pass Array to a Stored Procedure - C# .NET Side Next, we need to define the method to pass the data and execute the stored procedure from C# .NET. The first step is to convert our array of data into a comma-delimited string, which is what the stored procedure expects to receive. Depending on your data type, this code may vary. For this example, we are using a .NET collection. string myCodes = string.Empty; // Initialize a string to hold the comma-delimited data as empty foreach (MyItem item in MyCollection)
{
if (myCodes.Length > 0)
{
myCodes += ", "; // Add a comma if data already exists
} myCodes += "'" + item.Name + "'";
} The code above will create a string in the following format:
'One','Two','Three' Now that the collection has been converted to a string, we can pass the value as a parameter to the stored procedure by using the following code: using System;
using System.Data;
using System.Data.SqlClient; SqlConnection MyConnection = null;
SqlDataReader MyReader = null; try
{
// Create the SQL connection.
MyConnection = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"))
MyConnection.Open(); // Create the stored procedure command.
SqlCommand MyCommand = new SqlCommand("GetData", MyConnection); // Set the command type property.
MyCommand.CommandType = CommandType.StoredProcedure; // Pass the string (array) into the stored procedure.
MyCommand.Parameters.Add(new SqlParameter("@MyCodes", myCodes)); // Execute the command
MyReader = MyCommand.ExecuteReader(); // ...
}
catch (Exception excep)
{
}
finally
{
if (MyReader != null)
{
MyReader.Close();
MyReader.Dispose();
} if (MyConnection != null)
{
MyConnection.Close();
MyConnection.Dispose();
}
} Other Methods to Pass Arrays to an SQL Stored Procedure There are several other methods for passing arrays to SQL stored procedures, although they deal with increasing complexity. One such method includes parsing a comma-separated list of values into a temporary table which is then joined with a main table. Another method includes the usage of XML in MSSQL and the calling of sp_xml_preparedocument and sp_xml_removedocument. Note that the XML method requires the data that is passed in to be formatted in XML. For example: <ROOT>
<Ord MyID = "123"/>
<Ord MyID = "456"/>
<Ord MyID = "789"/>
</ROOT> One could always create a user-defined MSSQL function to parse a list of data and pass the appropriate values as required. The actual method chosen to pass arrays in MSSQL is ultimately up to the type of data you are dealing with in the core application. |