BetterWay to write this proc

  • Hi

    Here is my table and sample data:

    create table tab1(id int,oldId int, InsertedOn datetime)

    insert into tab1 values (99,NULL,getdate()-7)

    insert into tab1 values (17,14,getdate()-9)

    insert into tab1 values (14,12,getdate()-14)

    insert into tab1 values (12,null,getdate()-15)

    Now i need to write a proc which when called like this:

    exec MYProc 20--passed parameter id latest id

    Should return:

    idoldId

    12NULL

    1412

    1714

    2017

    Since 20 is my latest ID, I should get the complete chain for that id. I was able to achieve it using loop and union etc., but i know it can be writen in a better way in single query.

    Help me to get that single query...

    thanks

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Try this

    select * from tab1

    order by id

  • srikant maurya (6/19/2012)


    Try this

    select * from tab1

    order by id

    Where is the passed parameter used in thsi query? it will return everything including id=99, which I dont want when I pass 20

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Try this

    create proc somename

    (@paramname int

    )

    as

    begin

    select what ever

    from some table

    where id=@paramname

    end

    I sure you get the jist:w00t:

    ***The first step is always the hardest *******

  • Looks like you guys are missing my point. If I run this query, then I'll get only that row which has id=20.

    What I want is complete zigzag chain of latest id 20 as I posted in my original question

    SGT_squeequal (6/19/2012)


    Try this

    create proc somename

    (@paramname int

    )

    as

    begin

    select what ever

    from some table

    where id=@paramname

    end

    I sure you get the jist:w00t:

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • id and oldid is one-to-one? or many-to-one?

  • if you want everything previous, then wouldn't it simply be less than or euql to @param?

    create proc somename

    (@paramname int

    )

    as

    begin

    select what ever

    from some table

    where id <= @paramname

    end

    or are you talking about a hierarchy?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • First you need to add in your missing row that is in your results, but not in the sample data. Then use a recursive cte:

    USE tempdb

    GO

    DECLARE @tab1 TABLE (id int,oldId int, InsertedOn datetime)

    insert into @tab1 values (99,NULL,getdate()-7)

    insert into @tab1 values (17,14,getdate()-9)

    insert into @tab1 values (14,12,getdate()-14)

    insert into @tab1 values (12,null,getdate()-15)

    insert into @tab1 values (20,17,getdate()-5)

    ;WITH cte (id, oldid)

    AS (

    SELECT id, oldid FROM @tab1 WHERE id = 20

    UNION ALL

    SELECT t1.id, t1.oldid

    FROM @tab1 t1

    INNER JOIN cte c1

    ON c1.oldid = t1.id)

    SELECT *

    FROM cte

    ORDER BY id

    To make this a proc, change the 20 to a parameter.

    Jared
    CE - Microsoft

  • S_Kumar_S (6/19/2012)


    Hi

    Here is my table and sample data:

    create table tab1(id int,oldId int, InsertedOn datetime)

    insert into tab1 values (99,NULL,getdate()-7)

    insert into tab1 values (17,14,getdate()-9)

    insert into tab1 values (14,12,getdate()-14)

    insert into tab1 values (12,null,getdate()-15)

    Now i need to write a proc which when called like this:

    exec MYProc 20--passed parameter id latest id

    Should return:

    idoldId

    12NULL

    1412

    1714

    2017

    Since 20 is my latest ID, I should get the complete chain for that id. I was able to achieve it using loop and union etc., but i know it can be writen in a better way in single query.

    Help me to get that single query...

    thanks

    Ok, I think I get what you want, you would want to chase the 20-17, then 17 to 14, then 14 to 12, then 12 to the NULL. Is the final entry always NULL in the old id?

    Assuming it is, you need a temp table and a recursive call to get the chain, passing in the next "link" in the chain, all wrapped in a While Loop. Someone else might have a single statement approach, but I think this is a call for recursion. CTE might be an altarnative as well.

  • create table #tab1(id int,oldId int, InsertedOn datetime)

    insert into #tab1 values (99,NULL,getdate()-7)

    insert into #tab1 values (17,14,getdate()-9)

    insert into #tab1 values (14,12,getdate()-14)

    insert into #tab1 values (12,null,getdate()-15)

    insert into #tab1 values (20,17,getdate()-8)

    select distinct a.id,a.oldId

    from #tab1 a

    join #tab1 b on (a.oldid=b.Id) OR (b.id=a.Id)

    where a.id<=20

  • CREATE PROC myproc

    @param INT

    as

    begin

    select @param, MAX(ID) from tab1 WHERE ID < @param

    union

    select ID, oldid from tab1 where ID < @param

    end

  • Let me see if I can explain this better for the OP. It is irrelevant the value of the ID in terms of max, min, >, <... He wants to show the chain starting with a given ID. The data could just as well be:

    DECLARE @tab1 TABLE (id int,oldId int, InsertedOn datetime)

    insert into @tab1 values (99,NULL,getdate()-7)

    insert into @tab1 values (59,1008,getdate()-9)

    insert into @tab1 values (1008,2,getdate()-14)

    insert into @tab1 values (2,null,getdate()-15)

    insert into @tab1 values (20,59,getdate()-5)

    The point is that the op wants to either pass or find an "id" and then look at the corresponding "oldid". Then find the "id" that matches that "oldid" and find ITS "oldid" all the way until the original record which will not have an "oldid"; i.e. oldid IS NULL.

    Jared
    CE - Microsoft

  • Here is the proc:

    USE test

    GO

    CREATE TABLE tab1 (id int,oldId int, InsertedOn datetime)

    insert into tab1 values (99,NULL,getdate()-7)

    insert into tab1 values (17,14,getdate()-9)

    insert into tab1 values (14,12,getdate()-14)

    insert into tab1 values (12,null,getdate()-15)

    insert into tab1 values (20,17,getdate()-5)

    GO

    CREATE PROC myProc

    @param INT

    AS

    BEGIN

    ;WITH cte (id, oldid)

    AS (

    SELECT id, oldid

    FROM tab1

    WHERE id = @param

    UNION ALL

    SELECT t1.id, t1.oldid

    FROM tab1 t1

    INNER JOIN cte c1

    ON c1.oldid = t1.id)

    SELECT id, oldid

    FROM cte

    ORDER BY id

    END

    GO

    EXEC myProc 20

    Jared
    CE - Microsoft

  • This is what I needed but why CTE? we can only use query used in this CTE, right?

    SQLKnowItAll (6/19/2012)


    Here is the proc:

    USE test

    GO

    CREATE TABLE tab1 (id int,oldId int, InsertedOn datetime)

    insert into tab1 values (99,NULL,getdate()-7)

    insert into tab1 values (17,14,getdate()-9)

    insert into tab1 values (14,12,getdate()-14)

    insert into tab1 values (12,null,getdate()-15)

    insert into tab1 values (20,17,getdate()-5)

    GO

    CREATE PROC myProc

    @param INT

    AS

    BEGIN

    ;WITH cte (id, oldid)

    AS (

    SELECT id, oldid

    FROM tab1

    WHERE id = @param

    UNION ALL

    SELECT t1.id, t1.oldid

    FROM tab1 t1

    INNER JOIN cte c1

    ON c1.oldid = t1.id)

    SELECT id, oldid

    FROM cte

    ORDER BY id

    END

    GO

    EXEC myProc 20

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Sorry, I missed to see that you used CTE inside the query to make it nested.

    Thanks

    SQLKnowItAll (6/19/2012)


    Here is the proc:

    USE test

    GO

    CREATE TABLE tab1 (id int,oldId int, InsertedOn datetime)

    insert into tab1 values (99,NULL,getdate()-7)

    insert into tab1 values (17,14,getdate()-9)

    insert into tab1 values (14,12,getdate()-14)

    insert into tab1 values (12,null,getdate()-15)

    insert into tab1 values (20,17,getdate()-5)

    GO

    CREATE PROC myProc

    @param INT

    AS

    BEGIN

    ;WITH cte (id, oldid)

    AS (

    SELECT id, oldid

    FROM tab1

    WHERE id = @param

    UNION ALL

    SELECT t1.id, t1.oldid

    FROM tab1 t1

    INNER JOIN cte c1

    ON c1.oldid = t1.id)

    SELECT id, oldid

    FROM cte

    ORDER BY id

    END

    GO

    EXEC myProc 20

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply