SQL使用逗号分隔值和IN子句

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

我正在开发一个ASP.NET应用程序,并将一个字符串值“1,2,3,4”传递给一个过程,以选择那些IN(1,2,3,4)的值,但它说“转换失败时转换varchar值'1,2,3,4'到数据类型int。“

Here is the aspx code:

这是aspx代码:

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

8 个解决方案

#1

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

这将获得逗号分隔列表并将其与使用LIKE的表中的id(表示为',1,',',2,'等)进行比较

#2

If you dont want to use dynamic sql, the best way ive found is to create a function which turns a delimited string into a table, something like this works for an Integer list:

如果您不想使用动态sql,我发现的最好方法是创建一个将分隔的字符串转换为表的函数,类似于整数列表:

CREATE FUNCTION [dbo].[StringToIntList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS 
    @result TABLE (
        [ID] INT NULL)
AS
BEGIN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

Then use that in your sp:

然后在你的sp中使用它:

CREATE Procedure [dbo].[sp_getUserRoles](
   @pGroupIDs varchar(50)
    )
     As
    BEGIN
        SELECT * FROM CheckList_Groups Where id in (
           SELECT ID FROM dbo.StringToIntList(@pGroupIds,',')
       )
   End

#3

Sure it can't do that,

当然不能那样做,

The generated query would be sth like this

生成的查询将是这样的

SELECT * FROM CheckList_Groups Where id in ('1,2,3,4')

and sure it can't be executed.

并确定它无法执行。

you can build the query in your stored procedure then execute it with exec

您可以在存储过程中构建查询,然后使用exec执行它

'SELECT * FROM CheckList_Groups Where id in (' + @pGroupIDs + ')'

SELECT * FROM CheckList_Groups Where charindex(','+id+',' , @pGroupIDs)>0

but you first must add the ',' to start and end of your parameter in your c# code

但是首先必须在c#代码中添加','到参数的开头和结尾

#4

It is not possible to put those values (the comma separated string) in a parameter-value.

无法将这些值(逗号分隔的字符串)放在参数值中。

What you'll have to do, is to create the SQL Statement in your stored procedure dynamically, by string concatenation. You'll have to execute it with the sp_executesql stored procedure then.

您需要做的是通过字符串连接动态地在存储过程中创建SQL语句。您必须使用sp_executesql存储过程执行它。

 CREATE PROCEDURE [dbo].[getUserRoles]( @groupIds NVARCHAR(50) ) 
 AS BEGIN   
    DECLARE @statement NVARCHAR(255)

    SELECT @statement = N'SELECT * FROM CheckList_Groups Where id in ( ' + @pGroupIDs + N')'    

    execute sp_executesql @statement 
 END

Also, not that I named the SP getUserRoles instead of sp_getUserRoles. The reason is very simple: when you execute a stored procedure whose name starts with sp_, then SQL Server will first query the master database to find that stored procedure, which causes a performance hit offcourse.

而且,不是我命名SP getUserRoles而不是sp_getUserRoles。原因很简单:当您执行名称以sp_开头的存储过程时,SQL Server将首先查询master数据库以查找该存储过程,这会导致性能受到影响。

#5

The way you are trying to do this is slightly wrong. You will need to use EXECUTE in order to achieve this.

你试图这样做的方式有点不对。您需要使用EXECUTE才能实现此目的。

CREATE PROCEDURE [dbo].[sp_getUserRoles](@pGroupIDs nvarchar(50))
As
BEGIN         
    EXECUTE (N'SELECT * FROM CheckList_Groups Where id in (' + @pGroupIDs + ')';
END

#6

You need to use SP_executesql to achieve this functionllity

您需要使用SP_executesql来实现此功能

CREATE Procedure [dbo].[sp_getUserRoles](
   @pGroupIDs varchar(50)
    )
     As
    BEGIN

EXECUTE sp_executesql 
          N'SELECT * FROM CheckList_Groups Where id in (@pGroupIDs)',
          N'@level varchar(50)',
          @level = @pGroupIDs;

 End

#7

The IN clause can't take a bound parameter like that. What it's being given when the query is actually created is SELECT * FROM CheckList_Groups Where id in ('1,2,3,4'). Essentially the IN clause is being passed a single string.

IN子句不能采用这样的绑定参数。实际创建查询时给出的是SELECT * FROM CheckList_Groups其中id为('1,2,3,4')。本质上,IN子句传递一个字符串。

#8

First create function -

首先创建功能 -

Just run this code

只需运行此代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
    RETURN
END
GO

Then -

Use function in bracket with select statment -

使用括号中的功能选择参数 -

DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

#1