Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!