准备好的报表 - 在列表中

发布于 2024-10-17 13:52:05 字数 948 浏览 2 评论 0原文

可能的重复：
参数化 SQL IN 子句？

如何将以下查询转换为准备好的语句 (如果可能的话..）？

string allemails = "[email protected], [email protected], [email protected]"; //etc...
string query = "select UNIQUE_ID users where E_MAIL in (" + allemails + ")";
//run query....

我可以这样做吗：

OdbcCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "select UNIQUE_ID users where E_MAIL in (?)";

如果是这样，我应该添加到 cmd 参数集合中的参数是什么，如果是 varchar，我如何要求无限大小？

原文

Possible Duplicate:
Parameterizing a SQL IN clause?

How do I translate the following query to a prepared statement (if possible..)?

string allemails = "[email protected], [email protected], [email protected]"; //etc...
string query = "select UNIQUE_ID users where E_MAIL in (" + allemails + ")";
//run query....

Can I do something like:

OdbcCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "select UNIQUE_ID users where E_MAIL in (?)";

If so, what is the parameter I should add to the cmd parameters collection, and if it's varchar, how can I ask for unlimited size?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

溇涏 2024-10-24 13:52:05

如果您使用的是 OdbcCommand，则表明您正在使用 SQL Server 以外的数据库。 SQL Server 2008 引入了表值参数，这可能是一种解决方案。

假设您使用的数据库不支持此功能，则可以选择使用 gjvdkamp 建议的拆分功能。

如果您的数据库支持动态 sql（SQL Server 提供了 sp_executesql 存储过程用于此目的）您也可以考虑使用它。

您可能要考虑的最后一个选项（不太优雅）是允许有限数量的电子邮件地址，并允许每个地址通过自己的参数传递。然后，您可以在 where 子句中使用它们，如下所示。

where E_MAIL in (@Address1, @Address2, @Address3)

That you are using the OdbcCommand would indicate that you are using a database other than SQL Server. SQL Server 2008 introduced table valuded parameters which might have been a solution.

Assuming that the database that you are using does not support this feature, a split function such as the one that gjvdkamp suggested would be an option.

If your database support dynamic sql (SQL Server provides the sp_executesql stored procedure for this purpose) you could also consider using that.

A final option that you might want to consider (not very elegant) would be to allow for a limited number of email addresses and allow each to be passed via its own parameter. You would then use them in the where clause as follows.

where E_MAIL in (@Address1, @Address2, @Address3)

回复收藏 0 原文

世态炎凉 2024-10-24 13:52:05

嗯，很好，但我认为你不能那样做。

我的做法是重写查询以对一组选项使用联接，然后从拆分函数生成该组选项。

如需拆分，请查看此处： http://www.sqlteam.com/forums/topic .asp?TOPIC_ID=50648

那么SP看起来像这样

Create proc GetUsersByEmail
 @EmailList varchar(8000)
as

  select unique_id
  from users u
       inner join split(@emaillist,',') e on u.e_mail = e.data

实际上你也可以直接传递sql而不在数据库中创建SP，但我会选择SP。

问候 GJ

Hmm, good one, it don;t think you can do it like that.

My take would be to rewrite the query to use a join against a set of options, and you generate that set of options from a split function.

For a split look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Then the SP would look something like this

Create proc GetUsersByEmail
 @EmailList varchar(8000)
as

  select unique_id
  from users u
       inner join split(@emaillist,',') e on u.e_mail = e.data

Actually you could also pass the sql directly without creating an SP in the DB, but I'd go for a SP.

Regards GJ

回复收藏 0 原文

~没有更多了~