Thursday, August 30, 2007 7:08 AM
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))
Thursday, August 30, 2007 8:26 AM
Here it is,Code Snippet
alter function splittoint(@values varchar(8000), @delimiter varchar(10))
returns @result table (value int)
declare @v as varchar(8000);
while charindex(@delimiter,@values) <> 0
set @v = substring(@values,1,charindex(@delimiter,@values)-1);
insert into @result
set @values = substring(@values,charindex(@delimiter,@values)+1,len(@values))
insert into @result
Select * from splitToint('1,2,3,4,56,A',',')
Thursday, August 30, 2007 1:22 PMModerator
Arrays and Lists in SQL Server
Sunday, September 02, 2007 6:59 AM
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 !)
Sunday, September 02, 2007 5:34 PMModerator
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('...').
Monday, September 03, 2007 6:48 AM
“in” create a dynamic “OR” query.
There’s no “built in” way to create a dynamic “AND” query?
Monday, September 03, 2007 2:02 PMModerator
Yes, it is. Google for "relational division".
dbo.t1 as a
dbo.ufn_split('1, 3, 4, 5, 8, 9') as b
on a.c2 = b.c1
count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)
Tuesday, September 04, 2007 6:51 AMThanks!