How to Pass a Comma Separated List to a Stored Procedure

Passing a varchar full of comma delimited values to a SQL Server IN function

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

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
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.

Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/

----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    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!=''

RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

How to pass a comma separated list to a stored procedure?

If you're using Sybase 12.5 or earlier then you can't use functions. A workaround might be to populate a temporary table with the values and read them from there.

How to pass comma separated values to stored procedure in MySql?

You can pass comma separated values in procedures however you need to use prepared statement to use it, since the values you pass should be concatenated in the query.

delimiter //
   CREATE DEFINER=`test`@`%` PROCEDURE  `test`.`get_details`(
    in p_istudid int,
    in p_icourseid int,
    in p_branchid varchar(20)
    )
    BEGIN
    set @qry = concat('select .... and branch.id in (\'',p_branchid,'\')');
    prepare stmp from @qry;
    execute stmp ;
    deallocate prepare stmp;
    END;//
delimiter ;

Passing comma delimited string to stored procedure?

Don't do the split at all. Create a table valued parameter and pass this to your stored procedure. Then change your stored procedure to join to the table valued parameter.

Your sproc will end up looking like this:

CREATE PROCEDURE [dbo].[uspPages_HotelPrices_Lookup_Select] 
    @HotelCodes dbo.MyCodesTable READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM tPages_HotelPrices_Lookup a 
    INNER JOIN @HotelCodes b ON (a.ID = b.ID)
END

There are lots of good examples of using table values parameters on SO and the internet. A good method to get used to.

How to pass string of comma-separated numbers to stored procedure in condition for numeric field?

You can use dynamic sql.

create or replace PROCEDURE Fetch_Emp_Name(emp_id in varchar2) IS
v_result varchar2;
begin
    execute immediate
        'select Name from EMP where id in (' || 'emp_id' || ')'
    into
        v_result;             
end;    

Also you can use package dbms_sql for dynamic sql.

Update

Another approach. I think may be better.

create or replace PROCEDURE Fetch_Emp_Name(emp_id in varchar2) IS
v_result varchar2;
begin
    select 
        Name 
    from 
        EMP 
    where 
        id in
            (
                select 
                    to_number(regexp_substr(emp_id, '[^,]+', 1, level)) 
                from 
                    dual
                connect by regexp_substr(emp_id, '[^,]+', 1, level) is not null

            );
exception
    when no_data_found then
        -- error1;
    when too_many_rows then
        -- error2;                
end;    

How to pass comma separated input parameter to call stored procedure from another stored procedure

Since you are parsing a comma separated value to a int variable it doesn't work.

Try the below sp. Here used cursor to fetch the id one by one and it will call the sp UPDATE_DETAILS.

CREATE DEFINER=`root`@`localhost` PROCEDURE `RUN_JOB`()
BEGIN

    declare v_id int;
    declare done boolean default false;

    declare cur_id cursor for select id from table_1;

    declare continue handler for not found set done=true;

    open cur_id;

    call_loop: loop

        fetch cur_id into v_id;

            if done then

                leave call_loop;

            end if;

        call UPDATE_DETAILS(v_id);

    end loop;

    close cur_id;

    set done=false;

END

Using a comma-separated parameter in an IN clause

Try this one, Just need to add commas at the beginning and at the end of @params string.

Declare @params varchar(100) Set @params = ',param1,param2,param3,'

Select * from t
where CHARINDEX(','+cast(col1 as varchar(8000))+',', @params) > 0

SQL FIDDLE

---

Related Topics