SQL使用逗号分隔值与IN子句 [英] SQL use comma-separated values with IN clause
本文介绍了SQL使用逗号分隔值与IN子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我开发一个ASP.NET应用程序,并通过如1,2,3,4的字符串值转换成程序来选择那些IN(1,2,3,4)的值,但它说转换转换为varchar值'1,2,3,4'为数据类型int时失败。
下面是ASPX code:
私人无效fillRoles()
{
/ *阅读用户配置文件数据从数据库* /
数据库DB = DatabaseFactory.CreateDatabase(); 的DbCommand CMD = db.GetStoredProcCommand(sp_getUserRoles); db.AddInParameter(CMD@pGroupIDs,System.Data.DbType.String);
db.SetParameterValue(CMD@pGroupIDs,1,2,3,4); IDataReader的读卡器= db.ExecuteReader(CMD); DropDownListRole.DataTextField =集团;
DropDownListRole.DataValueField =ID; 而(reader.Read())
{
DropDownListRole.Items.Add((新的ListItem(阅读器[1]的ToString(),阅读器[0]的ToString())));
} reader.Close();
}
下面是我的程序:
CREATE PROCEDURE [DBO]。[sp_getUserRoles](@ pGroupIDs VARCHAR(50))
AS BEGIN
SELECT * FROM CheckList_Groups凡身份证件(@pGroupIDs)
结束
解决方案
下面是一个解决方法
,我发现你正试图达到什么
CREATE PROCEDURE [DBO]。[sp_getUserRoles](
@pGroupIDs VARCHAR(50)
)
如
开始
SELECT * FROM CheckList_Groups在哪里(','+ @pGroupIDs +','LIKE'%'+ CONVERT(VARCHAR,ID)+',%')
结束
这得到您的逗号分隔的列表,并将其与该ID的(这是重新psented像$ P $所以,1,
, ,2,
表中的使用等) LIKE
I am developing an ASP.NET application and passing a string value like "1,2,3,4" into a procedure to select those values which are IN (1,2,3,4) but its saying "Conversion failed when converting the varchar value '1,2,3,4' to data type int."
Here is the aspx code:
private void fillRoles()
{
/*Read in User Profile Data from database */
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("sp_getUserRoles");
db.AddInParameter(cmd, "@pGroupIDs", System.Data.DbType.String);
db.SetParameterValue(cmd, "@pGroupIDs", "1,2,3,4");
IDataReader reader = db.ExecuteReader(cmd);
DropDownListRole.DataTextField = "Group";
DropDownListRole.DataValueField = "ID";
while (reader.Read())
{
DropDownListRole.Items.Add((new ListItem(reader[1].ToString(), reader[0].ToString())));
}
reader.Close();
}
Here is my procedure:
CREATE Procedure [dbo].[sp_getUserRoles](@pGroupIDs varchar(50))
AS BEGIN
SELECT * FROM CheckList_Groups Where id in (@pGroupIDs)
END
解决方案
Here is a workaround
I found to do what you are trying to achieve
CREATE Procedure [dbo].[sp_getUserRoles](
@pGroupIDs varchar(50)
)
As
BEGIN
SELECT * FROM CheckList_Groups Where (',' + @pGroupIDs +',' LIKE '%,' + CONVERT(VARCHAR, id) + ',%')
End
This gets your comma delimited list and compares it to the id's(which are represented like so ',1,'
, ',2,'
etc) in the table using LIKE
这篇关于SQL使用逗号分隔值与IN子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文