Error using CSV string with IN statement

Question

When I run the following code

declare @aaa nvarchar(10)
set @aaa='1,2,3'
Select * from Customer where CustomerId in (convert(nvarchar,@aaa,10))

I get this error

Msg 8114, Level 16, State 5, Line 3
Error converting data type nvarchar to bigint.

Help plz

Answer 1

 Declare @SQL VarChar(1000)
 @SQL = 'Select * from Customer where CustomerId in (' + @aaa + ')'
 EXEC (@SQL)

Answer 2

You cannot use INwith cvs string. If you have Sql Server 2008, you can use the table value option (new in SQL Server 2008). Define it by creating the actual table parameter type:

CREATE TYPE IntTableType AS TABLE (ID bigint PRIMARY KEY)

Your procedure will be as follows:

Create Procedure up_TEST
    @Ids IntTableType READONLY
AS

SELECT * 
    from Customer where CustomerId in (SELECT ID FROM @Ids)

RETURN 0
GO

, . " SQL Server 2008 " , SQL Server. PRO CON :

" SQL Server 2005 Beyond, " Erland Sommarskog

split. :

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

TSQL, SQL Server, . , PRO CON .

Numbers Table , Numbers, 1 10000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Numbers , :

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

CSV :

declare @aaa nvarchar(10)
set @aaa='1,2,3'
Select * from Customer where CustomerId in (SELECT ListValue FROM dbo.FN_ListToTable(',',@aaa))

Answer 3

CSV IN, . IN 1,2,3 not '1,2,3', .

CSV, , CSV IN. SQL Server .

. .

CREATE FUNCTION dbo.SplitCSV (@CSVString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS

BEGIN

DECLARE @pos INT;
DECLARE @slice VARCHAR(8000);

SELECT @pos = 1;
IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;

WHILE @pos!= 0
BEGIN
SET @pos = CHARINDEX(@Delimiter,@CSVString);
IF @pos != 0
SET @slice = LEFT(@CSVString, @pos - 1);
ELSE
SET @slice = @CSVString;

IF( LEN(@slice) > 0)
INSERT INTO @temptable(Items) VALUES (@slice);

SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);
IF LEN(@CSVString) = 0 BREAK;
END
RETURN
END

Answer 4

User-Defined-Function, WHERE

DROP FUNCTION dbo.udf_ItemParse 
GO
CREATE FUNCTION dbo.udf_ItemParse 
(
    @Input VARCHAR(8000), 
    @Delimeter char(1)='|'
)
RETURNS @ItemList TABLE (
    Item VARCHAR(50) ,
    Pos int
)
AS
BEGIN

    DECLARE @Item varchar(50)
    DECLARE @StartPos int, @Length int
    DECLARE @Pos int

    SET @Pos = 0

    WHILE LEN(@Input) > 0
    BEGIN

        SET @StartPos = CHARINDEX(@Delimeter, @Input)

        IF @StartPos < 0 SET @StartPos = 0

        SET @Length = LEN(@Input) - @StartPos - 1

        IF @Length < 0 SET @Length = 0

        IF @StartPos > 0
        BEGIN
            SET @Pos = @Pos + 1
            SET @Item = SUBSTRING(@Input, 1, @StartPos - 1)
            SET @Input = SUBSTRING(@Input, @StartPos + 1, LEN(@Input) - @StartPos)
        END
        ELSE
        BEGIN
            SET @Pos = @Pos+1
            SET @Item = @Input
            SET @Input = ''
        END

        INSERT @ItemList (Item, Pos) VALUES(@Item, @Pos)
    END
    RETURN
END 
GO

declare @aaa nvarchar(10)
set @aaa='1,2,3'
Select * from Customer 
where 
CustomerId in (SELECT Item From Udf_ItemParse(@aaa, ','))

Answer 5

1,2,3 bigint.

, nvarchar, , ,

Select * from Customer where CustomerId in (1,2,3)

.

* , CustomerId ('1,2,3')

.

, , nvarchar

nvarchar ' . , 1,2,3