Multivalued parameter within T-SQL Query

Question

I am working on .aspx page that uses a t-sql query that uses a multivalued input parameter (a set of labnames) using an array that would store all the labnames prior to running the query.

I have the following parameters for the query.

        With c.Parameters
        .Add(New SqlParameter("@sdate", sdate.text))
        .Add(New SqlParameter("@edate", edate.text))
        .Add(New SqlParameter("@labname", SqlDbType.Text)).Value = labnamesparam.ToString
    End With

However, I still see that only one labname (3rd param in the order).

Any ideas on this?

Answer 1

You need to turn the contest of the array into a string. Here is a c# example, certainly not the only way to do it.

        System.Text.StringBuilder k = new System.Text.StringBuilder();
        foreach (string x in LABNAMES) {
            k.Append(x);
            k.Append(",");
        }
        .Add(New SqlParameter("@labname", SqlDbType.Text)).Value =k.ToString();

Your going to have to change your sql though you can't have a dynamic in clause like that. Old trick but not good practice is to turn the whole sql into a string and do an execute one it.

Answer 2

You might have to do a little bit more work in your stored procedure if you want to pass along an array of strings to it and perform a T-SQL "IN" operation.

This article has a very good example.

Answer 3

IF you use SQL Server 2008, you could use the "table-valued parameter" (TVP) feature.

Basically, in SQL Server 2008, you need to define a user-defined table type:

CREATE TYPE Customer AS 
    TABLE (id int, CustomerName nvarchar(50), postcode nvarchar(50))

and then use that in your stored procedure as a parameter:

CREATE Procedure AddCustomers(@customer Customer READONLY)

And then in your C# code, you'd create a DataTable variable of the same structure, and use that as the input parameter. That way, you can pass in any number of values, and any structure you like.

See these excellent blog posts for more information and extensive code samples:

• Guy Burstein
• Ben Hall
• SQL-Team

Marc

Answer 4

For SQL 2008 your should use a TVP, as recommended by Marc.

For SQL 2005 there are several techniques like using XML or using a comma delimitted list. A comprehensive analysis of each technique is kept by Erland Sommarskog on hi page at http://www.sommarskog.se/arrays-in-sql-2005.html.

For SQL 2000 the options are fewer, and again Erland has a comprehensive discussion of each at Arrays and Lists in SQL Server (SQL 2000 and Earlier).

I highly recommend Erland's articles, they've been the reference on the subject for many years now.