CODEWITHSUNDEEP
asp.netsqlsql-servert-sql
Santosh Chandavaram
Santosh Chandavaram

Reputation: 2500

Multivalued parameter within T-SQL Query

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?

Upvotes: 1

Views: 741

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294387

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.

Upvotes: 2

marc_s
marc_s

Reputation: 754993

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:

Marc

Upvotes: 0

Gratzy
Gratzy

Reputation: 9389

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.

Upvotes: 1

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107566

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.

Upvotes: 0

Related Questions