Stored Procedure to return multiple inputs between commas

Posted on 2008-11-03
Last Modified: 2012-05-05
I would like to be able to take input from a user that is separated by commas:

for example:


Then i want to use the values in a where clause taken from a variable in a stored procedure.

I will make the variable nvarchar (4000) and use it in a like clause in a query. This way i can get multiple values from teh user. Sometimes they may use one or up to 10 values. What is the best way that i can accomplish this goal ?
Question by:basile
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    with the function like below, you can do it easily:

    SELECT * FROM yourtable
    WHERE yourfield IN ( SELECT VALUE FROM dbo.ParmsToList('a,b,c,d', ','))
    create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
    returns @result TABLE (Value varchar(8000))
        declare @dx varchar(9)
        --declare @loops int
        -- set @loops = 0
         DECLARE @TempList table
              Value varchar(8000)
         if @delimiter is null  set @delimiter = ' '
         if datalength(@delimiter) < 1 set @delimiter = ' '
         set @dx = left(@delimiter, datalength(@delimiter)-1)
         DECLARE @Value varchar(8000), @Pos int
         SET @Parameters = @Parameters + @delimiter
         SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
         IF REPLACE(@Parameters, @delimiter, @dx) <> ''
              WHILE @Pos > 0 --and @Loops < 100
                  -- set @loops = @loops + 1
                   SET @Value = LEFT(@Parameters, @Pos - 1)
                   IF @Value <> ''
                        INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
                   SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
                   SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
         INSERT @result
         SELECT value
            FROM @TempList

    Open in new window

    LVL 60

    Expert Comment

    LVL 1

    Author Comment

    What if the passed in values are variable? For example, they user goes to a reporting services screen, and in teh Text Box, types in A,B,C,D

    How can i handle that situation in either of the above situations? It looks like the values are hardcoded, but if i want to pass in those values, it's a little bit confusing in teh above examples:

    so my query woudl look like this

    select * from table where field_name in dbo.ParmsToList(@input_variable) -- From stored procedure input variable.....will these examples handle this type of input?
    LVL 142

    Accepted Solution

    same, indeed:
    SELECT * FROM yourtable
    WHERE yourfield IN ( SELECT VALUE FROM dbo.ParmsToList(@inut_variable, ','))

    Open in new window

    LVL 39

    Expert Comment

    Yes both of the posted solutions will handle that.  I might as well throw my method in the mix too.

    You'll have to create the view (vw_Nums) first but that is included in the comments.  But if your database already has a numbers table, you can you that table name in place of vw_Nums and the number column in place of n in my query.
    if object_id('[dbo].[fn_DelimitedToTable]') is not null
         drop function [dbo].[fn_DelimitedToTable]
    create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
    returns @Values TABLE
         (ident         int not null identity primary key clustered
         ,thePosition   int not null
         ,theValue      nvarchar(max)
    *    Author:        Brandon Galderisi
    *    Last modified: 07-Oct-2008
    *    Purpose:       splits an input string (@DelimitedString) 
    *                   on a delimiter (@delimiter) and outputs 
    *                   a table of values.
    insert into @Values (thePosition,theValue)
    		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
    		from	dbo.vw_Nums
    			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
    			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
    -- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
    -- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
    -- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
    -- numbers table.
    Requires a nunbers table or this view:
    create view vw_Nums
    with   cte0 as (select 1 as c union all select 1), 
           cte1 as (select 1 as c from cte0 a, cte0 b), 
           cte2 as (select 1 as c from cte1 a, cte1 b), 
           cte3 as (select 1 as c from cte2 a, cte2 b), 
           cte4 as (select 1 as c from cte3 a, cte3 b), 
           cte5 as (select 1 as c from cte4 a, cte4 b), 
           nums as (select row_number() over (order by c) as n from cte5)
           select n from nums 
    Sample Usage:
    select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
    select theValue from [dbo].[fn_DelimitedToTable]('a','|')
    select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')

    Open in new window

    LVL 1

    Author Comment


    Is there a way to use your function for a wild card? for example:

    if they chose %aabnm% as one of the paramaters, and also they may not want to have a wild care liek abcd

    they want to be able to use these to search multiple items.... How can i use your function to do so?

    Thanks a million.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    SELECT t.* FROM yourtable t
    JOIN dbo.ParmsToList(@inut_variable, ',') l
      ON t.yourfield LIKE l.value 

    Open in new window


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now