How to i get result based on ids list passed as a varchar?

Question

I am passing ids list as a varchar(500) and based upon that ids records are required.My sql code is

declare @Ids varchar(500) = '12964,12965,12966'

select *
from tblBooks
where BookID in (@Ids)

where BookID is varchar(50).Number of Ids can be 100.Converting @Ids into int gives following error

Conversion failed when converting the varchar value '12964,12965,12966' to data type int

How do i find result as @Id are not converted into Int.

If your `id` is an integer, why do you try to pass them as a `varchar`? — Soner Gönül, Jan 29 '14 at 14:51
Your current plan has serious drawbacks. You do need help implementing it as is (because you can't change the design), or would you rather have help implementing it the right way? — Anon, Jan 29 '14 at 14:53
OP is asking how to pass a variable list as a parameter to a SQL statement. He tried to use a comma-separated value as the first approach. This is a common and intuitive first try. — Yuck, Jan 29 '14 at 14:54

GarethD · Accepted Answer · 2014-01-29T15:00:42.253

Use a table variable:

DECLARE @Ids TABLE (ID INT);
INSERT @Ids VALUES (12964),(12965),(12966);

SELECT *
FROM tblBooks
WHERE BookID in (SELECT ID FROM @Ids);

If you need to pass this to a procedure then you can use a table valued parameter:

CREATE TYPE dbo.ListOfInt AS TABLE (ID INT);
GO
CREATE PROCEDURE dbo.GetBooks @IDs dbo.ListOfInt READONLY
AS
BEGIN
    SELECT *
    FROM tblBooks
    WHERE BookID in (SELECT ID FROM @Ids);
END
GO

DECLARE @IDs dbo.ListofInt;
INSERT @Ids VALUES (12964),(12965),(12966);
EXECUTE dbo.GetBooks @Ids;

Or From c#

var table = new DataTable();
table.Columns.Add("ID", typeof(int));

// ADD YOUR LIST TO THE TABLE

using (var connection = new SqlConnection("Connection String"))
using (var command = new SqlCommand("dbo.GetBooks", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    var param = new SqlParameter("@Ids", SqlDbType.Structured);
    param.TypeName = "dbo.ListofInt";
    param.Value = table;
    command.Parameters.Add(table);
    connection.Open();

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // do something
        }
    }
}

Once the TYPE is in place, you don't even need to use a stored procedure. You can simply call a normal query:

using (var connection = new SqlConnection("Connection String"))
using (var command = new SqlCommand("SELECT * FROM tblBooks WHERE BookID IN (SELECT ID FROM @IDs)", connection))
{
    var param = new SqlParameter("@Ids", SqlDbType.Structured);
    param.TypeName = "dbo.ListofInt";
    param.Value = table;
    command.Parameters.Add(table);
    connection.Open();

    // ETC
}

Doing the split in c# using String.Split() and passing the list to SQL will be more efficient than any approach that does the split in SQL

This is the preferred approach. You haven't shown how this would be called from a C# application though. That's going to be the OP's next issue. — Yuck, Jan 29 '14 at 14:57
@Yuck Yes, I had just seen that, was already writing the edit when you commented. — GarethD, Jan 29 '14 at 15:01

score 2 · Answer 2 · answered Jan 29 '14 at 14:54

You can write the query as this:

declare @Ids varchar(500) = '12964,12965,12966'

select *
from tblBooks
where ','+cast(BookID as varchar(500))+',' like '%,'+@Ids+',%';

But you don't want to do that because the performance is bad -- the query cannot use indexes.

Three other options. Use dynamic SQL and plug the list directly into the query. Or use a split function to split the string. Or use a table variable:

declare @ids table (id int);
insert into @ids(id)
    select 12964 union all select 12965 union all select 12966;

select b.*
from tblBooks b
where b.BookId in (select id from @ids);

score 2 · Answer 3 · answered Jan 29 '14 at 14:58

This won't work. SQL Server does not split strings for you implicitly and there is no built in string split function in SQL Server either.

If you are driving this via C# you can use Table value parameters. You can also pass your query through Dapper-Dot-Net which will automatically parameterize an "In" query.

If you really must do this in T-SQL, you can also use a string splitting logic here is a relatively concise one.

SELECT i.value('./text()[1]', 'int') [id] into #ids 
FROM( values(CONVERT(xml,'<r>' + REPLACE(@Ids+left(@@dbts,0),',','</r><r>') +  '</r>')) ) a(_) 
CROSS APPLY _.nodes('./r') x(i)
select *
from tblBooks a
join #ids i on i.id = a.bookId

score 0 · Answer 4 · answered Jan 29 '14 at 14:58

Create this function:

CREATE FUNCTION [dbo].[SplitDelimiterString] (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))

RETURNS @ItemTable TABLE (Item VARCHAR(8000))

AS
BEGIN
    DECLARE @StartingPosition INT;
    DECLARE @ItemInString VARCHAR(8000);

    SELECT @StartingPosition = 1;
    --Return if string is null or empty
    IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 

    WHILE @StartingPosition > 0
    BEGIN
        --Get starting index of delimiter .. If string
        --doesn't contain any delimiter than it will returl 0 
        SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 

        --Get item from string        
        IF @StartingPosition > 0                
            SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
        ELSE
            SET @ItemInString = @StringWithDelimiter;
        --If item isn't empty than add to return table    
        IF( LEN(@ItemInString) > 0)
            INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            

        --Remove inserted item from string
        SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                     LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)

        --Break loop if string is empty
        IF LEN(@StringWithDelimiter) = 0 BREAK;
    END

    RETURN
END

Then call it like this:

declare @Ids varchar(500) = '12964,12965,12966'

select *
from tblBooks
where BookID in (SELECT * FROM dbo.SplitDelimiterString(@ids,','))

Interesting solution. Probably not the most efficient thing in the world, but kudos for getting the job done. — RubberDuck, Jan 29 '14 at 15:10

score 0 · Answer 5 · edited May 23 '17 at 11:56

0

one way is to cast int to varchar. many other ways....

select * from tblBooks where CAST(BookID as varchar(50)) in (@Ids)

related: Define variable to use with IN operator (T-SQL)

edited May 23 '17 at 11:56

Community

1
1

answered Jan 29 '14 at 15:06

Joe

1,649
12
10

How to i get result based on ids list passed as a varchar?

5 Answers5