Professional Web Applications Themes

help with query - Microsoft SQL / MS SQL Server

I have the follwing proc but it isn't doing what I want it to:) It updates entries for a link directory once they have been approved. I want it to update the totalLinks column on the link category and any parent categories. The Link Table contains a column ParentCatID which is its parents catID. Any help to get this finished would be greatly appreciated John ________ The Table: ________ [dbo].[tblLinks] ( [LinkID] [bigint] IDENTITY (1, 1) NOT NULL , [TypeID] [int] NULL , [LinkName] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL , [URL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Address1] [nvarchar] ...

  1. #1

    Default help with query

    I have the follwing proc but it isn't doing what I want it to:) It updates
    entries for a link directory once they have been approved. I want it to
    update the totalLinks column on the link category and any parent categories.
    The Link Table contains a column ParentCatID which is its parents catID. Any
    help to get this finished would be greatly appreciated

    John
    ________
    The Table:
    ________

    [dbo].[tblLinks] (
    [LinkID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [TypeID] [int] NULL ,
    [LinkName] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
    [URL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Address1] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
    [Address2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
    [PostCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Telephone] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [LinkDescription] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [CatID] [numeric](10, 0) NOT NULL ,
    [Active] [bit] NOT NULL ,
    [TotalClicks] [int] NOT NULL ,
    [Reviews] [int] NOT NULL ,
    [Rating] [float] NOT NULL ,
    [CreatedDate] [datetime] NOT NULL ,
    [CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL

    _____________
    The Stored Proc
    _____________

    CREATE PROCEDURE spAuthoriseLink
    (
    LinkID Numeric,
    URL nvarchar(255),
    LinkDescription nvarchar(255)
    )
    AS
    SET NOCOUNT ON
    DECLARE CurrentCatID As Numeric, Loop As Bit
    Select CurrentCatID = CatID From vLinks Where LinkID = LinkID
    UPDATE tblLinks SET URL=URl, LinkDescription=LinkDescription, Active = 1
    WHERE LinkID = LinkID
    SET Loop = 1
    WHILE Loop = 1 AND CurrentCatID > 0
    BEGIN
    IF EXISTS (SELECT CatID FROM vCategories WHERE CatID = CurrentCatID)
    BEGIN
    UPDATE tblCategories SET TotalLinks = TotalLinks + 1 WHERE CatID =
    CurrentCatID
    SELECT CurrentCatID = ParentID FROM vCategories WHERE CatID =
    CurrentCatID
    IF ROWCOUNT = 0
    SET Loop = 0
    END
    ELSE
    SET Loop = 0
    END

    GO


    John Guest

  2. #2

    Default Re: help with query

    Why not do this with aggregation? I didn't read all of you'r stuff, but I
    think you'll get my point...

    in the loop, do something like this:

    UPDATE tblCategories SET
    TotalLinks = (select count(linkid) from tblLinks where CatID =
    CurrentCatID)
    WHERE CatID = CurrentCatID

    and then maybe a loop on the parent categories, with
    update tblCat set
    TotalLinks = (select sum(TotalLinks) .... where parentcatid =
    currentcatid)
    where catid = currentcatid

    u c?

    :P

    Good Luck!

    Lars-Erik

    "John Pether" <com> wrote in message
    news:phx.gbl... 
    categories. 
    Any 
    NOT 

    CurrentCatID) 



    bonghead Guest

  3. #3

    Default Re: help with query

    1) Look up the nested set model for hierarchies; you can do this in one
    statement instead of a loop.

    2) Throw out everything you have done. Almost every data type you are
    using is dead wrong and will destroy your data integrity.

    Why do you think that a postal code anywhere on Earth is CHAR(50)? The
    ITU standard for a phone is not anywhere near CHAR(100). BIT data in
    SQL? This is a high level language, not assembly code. Hope you don't
    think that IDENTITY can be used as key, but without complete DDL, you
    might actrually have done that.

    You have links for which you do not know their type or their name, but
    you can describe them (see TypeID, LinkName and LinkDescription). Read
    ISO-11179, so you will stop using meaningless data element names. By
    definition, there is no such thing as a "type_id" -- an identifier is
    unique for an entity; a type is an attribute (NOT an entity) which takes
    on many possible values.

    You did not do any research, much less any design, before you throw this
    mess together. This thing is dangerous and you need to get some help
    with it before you go into production.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Help with Query




    Here is an output of my table
    grp_id range_id begin end range_desc
    6 1 -999999 25 25 and Under
    6 2 26 45 26 to 45
    6 3 46 999999 46 and Over
    10 1 1 1 One vehicle
    10 2 2 99 Two or more vehicles
    11 1 -9999999 0 Lapsed Coverage 08/15/03
    11 2 1 14 <= 2 wks in future
    11 3 15 9999999 > 2 weeks in the future


    What I need to do is verify that the ranges don't overlap for each
    grp_id and range_id. They can add/delete/change the whole table, but I
    have to create a SP to verify the ranges are still not overlapping.
    Cusor/case/both???
    Any help would be greatly appreciated.....
    Thanks
    Fred


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Fred Guest

Similar Threads

  1. Replies: 5
    Last Post: August 9th, 04:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 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