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.

我正在使用.aspx页面,该页面使用t-sql查询,该查询使用多值输入参数(一组labnames),使用一个数组,该数组将在运行查询之前存储所有实验室名称。

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).

但是,我仍然只看到一个labname(顺序中的第3个参数)。

Any ideas on this?

有什么想法吗?

4 个解决方案

#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.

你需要将数组的竞赛变成一个字符串。这是一个c#示例,当然不是唯一的方法。

        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.

你不得不改变你的sql虽然你不能有这样的动态in子句。旧技巧但不是很好的做法是将整个sql变成一个字符串并执行一个执行。

#2

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

对于SQL 2008,您应该按照Marc的建议使用TVP。

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.

对于SQL 2005,有几种技术,如使用XML或使用逗号分隔列表。 Erland Sommarskog在http://www.sommarskog.se/arrays-in-sql-2005.html的hi页面上对每种技术进行了全面分析。

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).

对于SQL 2000,选项较少,并且Erland再次对SQL Server(SQL 2000和早期版本)中的数组和列表进行了全面的讨论。

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

我强烈推荐Erland的文章,他们多年来一直是这个主题的参考。

#3

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.

如果要向其传递字符串数组并执行T-SQL“IN”操作,则可能需要在存储过程中执行更多操作。

This article has a very good example.

这篇文章有一个很好的例子。

#4

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

如果您使用SQL Server 2008,则可以使用“表值参数”(TVP)功能。

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

基本上,在SQL Server 2008中,您需要定义用户定义的表类型:

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.

然后在您的C#代码中,您将创建一个具有相同结构的DataTable变量,并将其用作输入参数。这样,您可以传入任意数量的值以及您喜欢的任何结构。

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

有关更多信息和广泛的代码示例,请参阅这些优秀的博文:

Marc

#1