Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    37

    Angry Unanswered: Tree Structured Query

    Hi Sql Server Techies,
    I have a table with two columns as follows
    Qid, Reply
    1,1.1
    1.1,1.11
    1.11,1.111
    1.111,1.1111
    2,2.1
    2.1,2.11

    Now i want to write a procedure to get the complete replies by passing question as an arguement to the stored procedure..(i.e) if i pass 1 as arguement i want 1.1,1.11,1.111,1.1111 as output.
    Please help me..I would be delighted if u can..I am strong in ORACLE Stuff...so need some tips related to sqlserver.
    Please send me a mail vjeedigunta@rediffmail.com

    Thanks and Regards
    SAI

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Here is something to get you going:

    set nocount on
    go
    if exists (select object_id('abc')) drop table abc
    go
    create table abc (a numeric(5,4), b numeric(5,4))
    go
    insert abc values (1,1.1)
    insert abc values (1.1,1.11)
    insert abc values (1.11,1.111)
    insert abc values (1.111,1.1111)
    insert abc values (2,2.1)
    insert abc values (2.1,2.11)

    select * from abc
    go

    declare @x varchar(50)
    declare @y numeric(6,5)

    set @y = 1
    select @x = ''

    while (@@rowcount > 0) begin
    select @x = @x + ',' + CAST(b AS VARCHAR),
    @y = b
    from abc
    where a = @y
    end

    select SUBSTRING(@x,2,DATALENGTH(@x))

    Just a note, the @@rowcount works the first time because I used a SELECT statement to set the value of @x. You can wrap the code with a create proc and have @x as in input parameter with OUTPUT option.
    Code:
    create proc usp_tree (
               @x varchar(50)  OUTPUT
               @y numeric(6,5)
               )
    as
    set nocount on
    
    select @x = ''
    
    while (@@rowcount > 0) begin
    	select @x = @x + ',' + CAST(b AS VARCHAR),
    	       @y = b
    	from   abc
    	where  a = @y
    end
    
    select @x = SUBSTRING(@x,2,DATALENGTH(@x))
    MCDBA

Posting Permissions

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