Passing Delimited values to SQL Server stored procedure for IN operator. How?

Question

I need to pass the parameter (@name) as string 'Alex','david','crowner' to the query in a stored procedure.

The query in the stored procedure looks like this:

select * 
from employees 
where name in (@name)

The value for @name parameter that would be passed would be something like

'Alex','david','crowner'

How could I handle this in my stored procedure to find the names in a table with IN operator?

It would be far better to use (ideally) table-valued parameters or XML - data types *designed* to contain multiple values. — Damien_The_Unbeliever, Jul 14 '13 at 06:56
You can use something like `select * from employees where name in (Select * from dbo.F_SplitAsStringTable(@name))` a related function (need to be adapted) can be found here [F_SplitAsIntTable](http://stackoverflow.com/a/16993267/1699210) — bummi, Jul 14 '13 at 07:06

score 1 · Answer 1 · answered Jul 14 '13 at 11:04

In SQL Server 2008 and later, you can use a table valued parameter. In the database, you have to create a table type. For example:

-- Drop old example definitions
if exists (select * from sys.procedures where name = 'TestProcedure')
    drop procedure TestProcedure
if exists (select * from sys.types where name = 'TestTableType')
    drop type TestTableType
if exists (select * from sys.tables where name = 'TestTable')
    drop table TestTable
go    
-- Create example table, type and procedure
create table TestTable (id int identity, name varchar(50))
create type TestTableType as table (name varchar(50))
go
insert TestTable values ('Bill'), ('George'), ('Barrack')
go
create procedure dbo.TestProcedure
    @List TestTableType readonly
as
select  *
from    TestTable
where   name in 
        (
        select  name
        from    @List
        )
go

In C#, you can pass a DataTable as a table-valued parameter:

var listTable = new DataTable();
listTable.Columns.Add("Name", typeof(string));
listTable.Rows.Add("Bill");
listTable.Rows.Add("George");

var listParameter = new SqlParameter();
listParameter.ParameterName = "@List";
listParameter.Value = listTable;

using (var con = new SqlConnection("Server=localhost;Database=test;" + 
                                   "User Id=testuser;Password=testpassword;"))
{
    var com = con.CreateCommand();
    com.CommandText = "dbo.TestProcedure";
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.Add(listParameter);
    con.Open();
    using (var read = com.ExecuteReader())
    {
        while (read.Read())
            Console.WriteLine(read["name"]);
    }
}

The amount and complexity of code required for even a single table-valued parameter is no complement for the SQL Server designers.

I'm not sure what you'd think would be much cleaner in terms of implementation over how the SQL Server designers built the feature. And I think a single table-valued parameter would, by far, be the general use case - whether it has one column or fourteen, you're likely to want to pass a single parameter - unless you're trying to replicate an entire database. — Damien_The_Unbeliever, Jul 14 '13 at 15:36
@Damien_The_Unbeliever: On the C# side, accept any enumberable and construct a table variable from its public properties. On the SQL side, remove the `create type` and `read_only`, and declare the parameter inline, like `create proc dbo.TestProc(@t table (id int))`. — Andomar, Jul 14 '13 at 15:40

score 0 · Answer 2 · answered Jul 14 '13 at 06:52

0

you can pass in a single string to the parameter and inside the body of the stored proc use function(s) like charindex and substring and replace to do what you want

answered Jul 14 '13 at 06:52

tristan625

13
1
3

WolfiG · Answer 3 · 2015-07-23T19:18:19.697

After some research I stumbled over a Code Project thread which was the key to solve the problem. With the help of the above thread I wrote the following stored procedure:

CREATE PROCEDURE [dbo].[myWorkingProcedure]    
 @inputList nvarchar(MAX)    
AS       
DECLARE @SetPoint INT    
DECLARE @VALUE nvarchar(50)    
CREATE TABLE #tempTab (id nvarchar(50) not null)
BEGIN    
 SET NOCOUNT ON;
 WHILE PATINDEX('%,%',@inputList) > 0 <-- Drive loop while commata exist in the input string
 BEGIN
  SELECT  @SetPoint = PATINDEX('%,%',@inputList) <-- Determine position of next comma
  SELECT  @VALUE = LEFT(@inputList , @SetPoint - 1) <-- copy everything from the left into buffer
  SELECT  @idList = STUFF(@inputList, 1, @SetPoint, '') <-- throw away the stuff you copied
  INSERT INTO #tempTab (id) VALUES (@VALUE) <-- put value in buffer table
 END
 INSERT INTO #tempTab (id) VALUES (@inputList) <-- insert last value in the input list to buffer
 BEGIN
  SELECT * FROM myTable
  WHERE myColumn IN (SELECT id FROM #tempTab) <-- Do the select
   DROP TABLE #tempTab <-- throw buffer table away
 END    
END    
GO

Passing Delimited values to SQL Server stored procedure for IN operator. How?

3 Answers3