none
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

     

     

    Thursday, August 30, 2007 7:08 AM

Answers

  • 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

    Monday, September 03, 2007 2:02 PM

All replies

  •  

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

     

     

    Thursday, August 30, 2007 8:26 AM
  • Arrays and Lists in SQL Server

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

     

     

    AMB

    Thursday, August 30, 2007 1:22 PM
  • 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 OROR ID=val_n

    How can I create a query that is equal to:

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

    (without sp_exec !)

    Thanks

     

     

    Sunday, September 02, 2007 6:59 AM
  • 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

    Sunday, September 02, 2007 5:34 PM
  •  

    “in” create a dynamic “OR” query.

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

    Monday, September 03, 2007 6:48 AM
  • 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

    Monday, September 03, 2007 2:02 PM
  • Thanks! Smile

     

    Tuesday, September 04, 2007 6:51 AM