Convert comma separated string into table

We all use stored procedure ,
And Some Time we need to convert comma separated string into table.

assume that u have Cars table and primary key is Called CarID

and you want to write query that select all Cars that have Ids (11,12,13)
then you will write

SELECT * FROM [lkp_Cars] WHERE CarID  IN (11,12,13)

you will got BMW,Hyundai,Honda

assume you have scenario -> user select from interface multiple cars and you to run query

select * from lkp_cars where carID in (Selected cars)

 So you need to pass all selected cars from interface to the query by concatting  all carID+","+carID +","...

the point is that  i need to convert comma separated string  into table
so create the next function and use in any sql query

Create FUNCTION ConvertCommaSepratedToTable
(
      @IDsList varchar(500 )
)
RETURNS
@ParsedList table
(
      ID int
)
AS
BEGIN
       DECLARE @ID varchar (10), @Pos int

       SET @IDsList = LTRIM( RTRIM(@IDsList ))+ ','
       SET @Pos = CHARINDEX( ',', @IDsList , 1)

       IF REPLACE (@IDsList, ',', '') <> ''
       BEGIN
             WHILE @Pos > 0
             BEGIN
                   SET @ID = LTRIM( RTRIM(LEFT(@IDsList , @Pos - 1)))
                   IF @ID <> ''
                   BEGIN
                         INSERT INTO @ParsedList ( ID)
                         VALUES (CAST (@ID AS int)) --Use Appropriate conversion
                   END
                   SET @IDsList = RIGHT(@IDsList , LEN (@IDsList) - @Pos )
                   SET @Pos = CHARINDEX( ',', @IDsList , 1)

             END
       END   
       RETURN
END

annd now modify you query to

select * from lkp_cars where carID in (ConvertCommaSepratedToTable(IdsString))

Enjoy

Anas Saad
Senior Software Engineer