Professional Web Applications Themes

simple stored proc help... - Microsoft SQL / MS SQL Server

I have a table'(catlist) with 3 columns, catid, parentid and categoryname. I have a stored proc and am passing in catid but want to get the parent category name. The parentid and catid are foreign keys. this is what i've got but it doesn't work!!! CREATE PROCEDURE dbo.GetParentCategory ( CatID int ) AS select parent.categoryname from catlist cat, catlist parent where cat.catid = catid and cat.parentid = parent.parentid GO ************************************************** ******************** Sent via Fuzzy Software http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources......

  1. #1

    Default simple stored proc help...

    I have a table'(catlist) with 3 columns, catid, parentid and categoryname. I have a stored proc and am passing in catid but want to get the parent category name. The parentid and catid are foreign keys.

    this is what i've got but it doesn't work!!!
    CREATE PROCEDURE dbo.GetParentCategory
    (
    CatID int
    )
    AS
    select parent.categoryname
    from catlist cat, catlist parent
    where cat.catid = catid and cat.parentid = parent.parentid
    GO

    ************************************************** ********************
    Sent via Fuzzy Software http://www.fuzzysoftware.com/
    Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
    Guest

  2. #2

    Default Re: simple stored proc help...

    Please always put the sample data, desired result and table structures.
    from your question and SP I'm not understanding well. I got 2 feelings as:
    1) You have some other tables in which you have defined Parentid and its
    category name and catid and its description/name.

    2) you have only 1 table i.e. catlist and only parentid is foreignkey to
    self table's (catlist) with reference to catid.

    if my number 1 understanding is correct, then pl. let me know the tabel
    structure and reference to that.

    if number 2 understanding is correct then try this change:

    select parent.categoryname
    from catlist cat join catlist parent on cat.parentid = parent.catid
    where cat.catid = catid

    OR

    select parent.categoryname
    from catlist cat, catlist parent
    where cat.catid = catid and cat.parentid = parent.catid


    HTH

    Falik



    wrote in message news:phx.gbl... 
    I have a stored proc and am passing in catid but want to get the parent
    category name. The parentid and catid are foreign keys. 
    ASP.NET resources...


    Falik Guest

  3. #3

    Default Re: simple stored proc help...

    As the other poster said, more and better definition get better, quicker
    answers. And phrases like "doesn't work" are understandable to the person
    experiencing the problem but have little meaning to everyone else.

    In this case, you are not joining correctly to the parent row: the child
    row has a FK back to another row in the same table - you need to join the
    parent ID of the child to the PK of the parent.

    where cat.catid = catid and cat.parentid = parent.catid

    wrote in message news:phx.gbl... 
    I have a stored proc and am passing in catid but want to get the parent
    category name. The parentid and catid are foreign keys. 
    ASP.NET resources...


    Scott Guest

  4. #4

    Default Re: simple stored proc help...

    Try using "parent.catid" instead of "parent.parentid". Here's the code:

    select parent.categoryname
    from catlist as cat
    inner join catlist as parent on cat.parentid = parent.catid
    where cat.catid = catid

    wrote in message news:phx.gbl... 
    I have a stored proc and am passing in catid but want to get the parent
    category name. The parentid and catid are foreign keys. 
    ASP.NET resources...


    Anthony Guest

Similar Threads

  1. Simple Stored Proc Question
    By bzydaddy in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 2nd, 01:45 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. Simple stored proc in query question
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:20 PM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139