Error using CSV string with IN Operator

When I run the following code

declare @aaa nvarchar(10)
set @aaa='1,2,3'
Select * from Customer w开发者_如何学Chere 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.

plz help

You can't use IN with a cvs string. If you have Sql Server 2008, you may use a table values parameter (new in SQL Server 2008). Set it up by creating the actual table parameter type:

CREATE TYPE IntTableType AS TABLE (ID bigint PRIMARY KEY)

Your procedure would then be:

Create Procedure up_TEST
    @Ids IntTableType READONLY
AS

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

RETURN 0
GO

if you can't use table value parameters, see: "Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

You need to create a split function. This is how a split function can be used:

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

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

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)

Once the Numbers table is set up, create this split function:

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 

You can now easily split the CSV string and use it in your query:

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

You cannot pass a CSV into IN like that. IN expects 1,2,3 not '1,2,3', which is a huge difference.

If you have a CSV, you'll have to create a function that splits a CSV into a table, and pass the result of that into IN. There is no such function out of the box in SQL Server.

There are plenty of solutions for this online, though. Here's one.

_{Excerpt from link}

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

You should use a User-Defined-Function to split your string into a table and use that in the WHERE clause

Here is the code for the function

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

This is how you can use it

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

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

The value 1,2,3 could not be converted to bigint.

And this is normal because that is a nvarchar value, that what you are trying to do is to call statement like this

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

Here You fetch three entries.

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

and here you trying to fetch one object.

The operation that You want to perform is impossible from that nvarchar

the convert function can only change a the nvarchar 'charater representation' to number representation. and the is no number that look like 1,2,3