Coma separated string value as function parameter

Question

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

 

 

Answer 1

 

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',',')

 

 

Answer 2

Arrays and Lists in SQL Server

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

 

 

AMB

Answer 3

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

 

 

Answer 4

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

Answer 5

 

“in” create a dynamic “OR” query.

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

Answer 6

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

Answer 7

Thanks!