Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unhappy Unanswered: stored procedure problem

    Hi,
    in database i have in table 'A' field 'a' that can have several values ( table 'B' holds value of 'a' and foreign key - an id of table A), i.e. relation A:B is 1:n.
    I have to write stored procedure that creates two columns: in first column 'a' field and in second column comma separated list of values of a.
    I'll give an example.
    Table A:
    id field_name
    1 food
    2 drink

    Table B:

    id value foreign_key
    1 meat 1
    2 potato 1
    3 cola 2
    4 vodka 2

    The result of stored procedure:

    A_id field_name values_list
    1 food {meat, potato}
    2 drink {cola, vodka}


    I'm really stuck, any help is appreciated.
    Thank you
    Last edited by yuliam; 05-19-04 at 12:01.
    Thank you
    Yulia

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    drop table test
    drop table test2
    go
    create table test(id int, name varchar(25))
    create table test2(id int,name varchar(25),nameid int)
    go
    insert test values(1,'f')
    insert test values(2,'d')

    insert test2 values(1,'bred',1)
    insert test2 values(2,'bred2',1)
    insert test2 values(3,'cola',2)
    insert test2 values(4,'cola2',2)

    drop function getit
    go
    create function getit(@id int)
    returns varchar(8000)
    as
    begin
    declare @ret varchar(8000)
    set @ret=''
    select @ret=@ret+[name]+',' from test2 where nameid=@id
    return(@ret)
    end
    go
    select name,dbo.getit(id) from test

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    Thank you very much, this function does exactly what i need....
    I never used UDF before, can it be called from vb code? (using ado)
    Thank you
    Yulia

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    set rs = conn.execute("select A_id=id, field_name, value_list=dbo.getit(id) from table_a")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •