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!
