Pass list or array as a parameter to a Sql query

Like most developers my first Sql query was something like:

SELECT * FROM TABLE WHERE FIELD = 'value'

Which is a good starting point to learn Sql. After some time, I combined programming and Sql together, and wrote something which most developers will recognize:

rs.open "Select firstn* from Employees where lastname like '" & textbox1.Text & "%'" , connection

Which is BAD. For a testprogram it is okay, but for a “real” application this is not-done (sql-injection). Quickly I learned to use parameterized queries.
There is case where you can’t use parameters, that is when you need to pass a list/array/collection to a query:

SELECT * FROM TABLE WHERE id IN (1,2,3,4,5,6,7,8)

You can pass “1,2,3,4,5,6,7,8” as parameter is you put in in a varchar and split it using a custom function, but then you re-introduce the sql-injection risk again.
Since msSql 2008 there is a decent solution for this problem, “table valued parameters”. It works quite simple:
First you have to define a “custom-type” in the Sql database. Most examples show very specific custom-type, causing you to create 1 for every query. I created a “generic” custom-type:

  CREATE TYPE TableTypeHelper AS TABLE
  (                     
        NUMBER INT NULL,                
        string VARCHAR(MAX) NULL
  )

In this type I can put a number of a string, this is enough for all my “list to sql parameter” problems. Then you need to create a query of a stored procedure that uses this type as an input:

CREATE PROCEDURE TestProcedure
	@TableInput TableTypeHelper READONLY
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM t_Test WHERE ndId IN (SELECT NUMBER FROM  @TableInput)
END

Most important is the READONLY option. This type of parameter is read only for Sql, you can’t modify it in the stored procedure. You can of course use a join instead of an in, if you prefer.
Last you need to pass this parameter from the code to Sql:

DataTable dt = new DataTable("TableTypeHelper");        //Create a new datatable, tablename is equal to type we created
dt.Columns.Add("number", typeof(Int32));                //Add the columns, to this datatable, same name, type and order
dt.Columns.Add("string", typeof(string));
//Fill the datatable with the data you need to pass. Probarly: foreach(var x in y) {dt.rows.add(x.id,null);}
dt.Rows.Add(1,null);
dt.Rows.Add(3,null);
dt.Rows.Add(99,null);
SqlCommand cmd = new SqlCommand("TestProcedure", con); //Create the sql command (the stored procedure we created)
cmd.CommandType = CommandType.StoredProcedure;         //Set the correct commandtype
SqlParameter parameter = new SqlParameter();           //Create the parameter
parameter.ParameterName = "@TableInput";               //Name of this parameter
parameter.SqlDbType = System.Data.SqlDbType.Structured;//Type if the parameter is "Structured"
parameter.TypeName = "TableTypeHelper";                //Typename is the name of the type we created
parameter.Value = dt;                                  //Values is ths datatable we created
cmd.Parameters.Add(parameter);                         //Add the parameter to the command
SqlDataReader dr = cmd.ExecuteReader();                //Execute the command (you can also use dataset.fill)
while (dr.Read())
{
//Do something
}

You, of course need to add errorhandling and all other stuff.
Good luck using this

Comments are closed.