OK, I am posting the scripts for the SPs.
You should only look at the process of the SPs, to really use them, you will have to
create your own tables to work with. And modify the column and table names in the
SPs...
I was going to post the whole structure of the db as well, but it will take me to long
to modify and adjust for posting.
If anyone sees any faults in this script or knows a better way of doing it, I would be
happy to hear about it. It took me a long time to get these SPs to create a fast and
efficient hierachy..
While we are talking about SPs, anyone got any good solutions to client side cursors?
i.e. working with a cursor in the db is fine and dandy, but as soon as you transfer
the data to the client (web) it looses it's cursor features. I was working on a cursor
that doesn't loose it's state and maintain the session with the client, but it ended
up using to many temp tables...
I hope I'm not off topic here, I know its a CF list, but after all I am a CF
programmer ;-))
Stored Proc 1
---------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/******
Object: Stored Procedure dbo.spTree Script Date: 22/01/2003 9:51:54 AM
Author: Taco Fleur
******/
CREATE Procedure spTree
(
@parentID INTEGER
)
AS
SET NOCOUNT ON
DECLARE @table VARCHAR(64), @sql NVARCHAR(1024), @position INTEGER
SET @position = 0
SET @table = '##spTree' + CONVERT( VARCHAR(4), @@SPID )
SET @sql = 'CREATE TABLE ' + @table
+' ('
+' position INTEGER, '
+' pkReplyID INTEGER, '
+' fkParentID INTEGER, '
+' fkThreadType TINYINT, '
+' replyBody VARCHAR(8000), '
+' approved BIT, '
+' dateStamp SMALLDATETIME '
+')'
EXECUTE sp_executesql @sql
-- Get all immediate replies to the thread
-- threadType = 1 AND fkParentID = threadID
EXECUTE spTreeBuild '1', @parentID, @table, @position
-- The tree is now ready in the temp table.
-- Construct SQL statement for selecting from table.
SET @sql = 'SELECT * FROM ' + @table
-- Execute SQL to select.
EXECUTE sp_executesql @sql
-- Construct SQL statement for dropping the temp table.
SET @sql = 'DROP TABLE ' + @table
-- Execute SQL to drop the table.
EXECUTE sp_executesql @sql
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------------------------
Stored Proc 2
---------------------------------------------
/****** Object: Stored Procedure dbo.spTreeBuild Script Date: 22/01/2003 9:52:11
AM ******/
CREATE Procedure spTreeBuild
(
@threadType TINYINT,
@parentID INTEGER,
@table VARCHAR(64),
@position INTEGER
)
AS
SET NOCOUNT ON
DECLARE @replyID INTEGER, @replyBody VARCHAR(8000), @approved BIT, @dateStamp
SMALLDATETIME
DECLARE @sql NVARCHAR(1024)
DECLARE @children CURSOR
SET @position = @position + 1
EXECUTE spTreeChild @parentID, @threadType, @children OUTPUT, @position
-- Loop to parse the list of activities obtained in cursor.
WHILE( 1 = 1 )
BEGIN
-- Fetch the details for next activity.
FETCH NEXT FROM @children
INTO @position, @replyID, @parentID, @threadType, @replyBody, @approved, @dateStamp
-- Check if fetch is valid, i.e., there are more records.
IF @@FETCH_STATUS = -1 BREAK
SET @sql = 'INSERT INTO ' + @table + ' VALUES('
+ CAST(@position AS VARCHAR(10)) + ','
+ CAST(@replyID AS VARCHAR(10)) + ','
+ CAST(@parentID AS VARCHAR(10)) + ','
+ CAST(@threadType AS VARCHAR(10)) + ','
+ '''' + @replyBody + ''','
+ CAST(@approved AS VARCHAR(10)) + ','
+ '''' + CAST(@dateStamp AS VARCHAR(20)) + ''')'
EXECUTE sp_executesql @sql
EXECUTE spTreeBuild '2', @replyID, @table, @position
END
-- Close the cursor.
CLOSE @children
-- Deallocate the cursor.
DEALLOCATE @children
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------------------------
Stored Proc 3
---------------------------------------------
/****** Object: Stored Procedure dbo.spTreeChild Script Date: 22/01/2003 9:52:24
AM ******/
CREATE Procedure spTreeChild
(
@parentID INTEGER,
@threadType TINYINT,
@child CURSOR VARYING OUTPUT,
@position INTEGER
)
AS
SET NOCOUNT ON
SET @child = CURSOR
FOR SELECT @position AS position,
pkReplyID,
fkParentID,
fkThreadType,
replyBody,
approved,
dateStamp
FROM tblForumReply
WHERE (fkParentID = @parentID)
AND (fkThreadType = @threadType)
ORDER BY datestamp
OPEN @child
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------------------------
I would appreciate it if these stored procs won't go any further than this list, when
time comes I'd like to publish it myself ;-))
Taco Fleur
> This email is to be read subject to the disclaimer below.
>
> Taco,
>
> The one table approach you describe is something we use too, so those
> stored procedures sound interesting to me. Could you please send me a copy
> of them?
>
> Cheers
>
> Vik
> ---------------------
> Viktor Radnai
> Web Developer, National E-Commerce, Ernst & Young
> Direct: +61 2 9248 4361
>
>
>
>
>
> [EMAIL PROTECTED]
>
> Sent by: To: "CFAussie Mailing
>List" <[EMAIL PROTECTED]>
> [EMAIL PROTECTED] cc:
>
> mon.com.au Subject: [cfaussie]
>Re: UUID vs Integer with Primary keys..
>
>
> 22/01/2003 10:06 AM
>
> Please respond to "CFAussie
>
> Mailing List"
>
>
>
>
>
>
>
>
> Scott,
>
> I'm not sure why you would want to spread your data across 3 tables.
>
> I always use the following db structure for a Tree Hierachy.
>
> pkWhateverID [integer pk identity] / fkParentID [integer] / textColumn
> [varchar(X)]
>
> I use 3 very small and simple stored procedures that create a temp table
> and create the sorted output I need to display. I'd be happy to send you a
> copy of these 3 stored procedures if it would be of any help to you?
>
> The disadvantage I see is that with the structure you got now you can not
> go down the Tree hierachy further than the sibling. With the above
> structure you can go down the hierachy as far as you need to.
>
> I would say the negative effect of using UUID would be:
> - It takes longer to create than an incrementing integer
> - I believe there is still a 0.01 % change that you will run into a
> duplicate
> - It takes more space to store a UUID instead of an integer
>
> Hey, maybe I am way of here and don't exactly understand what needs to be
> done... In anycase if I am on the right track let me know if I can help..
>
> Taco Fleur
>
> PS. sounds like you got the perfect job being able to work on thsoe sites.
>
> > Sorry to ask probably a question thats had a debate before, but.. i will
> so shutup and read. :D
> >
> > Whats the negative sideeffects of using UUID instead of Integer
> (autonumber) for primary keys in a table. The reason i bring this up, as I
> always use a parent-child-sibling-infant type relationship amongst various
> tables, one example off the top of my head was a awhile back where i had 3
> tables that basically provide:
> >
> > - Category (parent table)
> > - Category (parent table)
> > ---- File (child table)
> > ---- File (child table)
> > ------- file versions (sibling table)
> > ------- file versions (sibling table)
> > ------- file versions (sibling table)
> > ---- File (child table)
> > ---- File (child table)
> > ------- file versions (sibling table)
> > ------- file versions (sibling table)
> > ------- file versions (sibling table)
> > - Category (parent table)
> > ---- File (child table)
> > ---- File (child table)
> > ---- File (child table)
> > ---- File (child table)
> > - Category (parent table)
> >
> > This situation called for the ability to not only associate 3 different
> tables into a heircahy based dataset, but it also called for i think 3-4
> other tables that provided other pieces of information per level, and in
> doing this i was forced to use a big meaty looking SQL SP, that made use of
> both UNIONs and Views, which worked great.
> >
> > I did find that if i simply relied on the Autonumber system, the heirachy
> would be out of sync, as in the joining field for the child had the same
> value as the parent pk, but having said that, the child pks also had the
> same value and so a child brach would mistakenly associate its children
> under another child?
> >
> > ie:
> > parentID = 2
> >
> > childID = 1
> > childParent = 2
> >
> > childID = 2
> > childParent = 2
> >
> > childID = 3
> > childParent = 2
> >
> > even though the childParent = 2, i did find that child referred to
> childID = 2 as its parent?
> >
> > I did manage to find away around this so i didn't have to basically
> redesign the entire db, but i'm about to embark on a bigger project
> (probably the biggest i have undertaken yet) and i so want this to be a
> finely tuned effecient running machine, and having remembering this
> scenario, i felt a bit nervous with simply utilising an "autonumbered"
> system or even a seperate table which has a running pool of numbers that
> you take from.
> >
> > Atleast with UUID i know that no matter what no other rowID through-out
> the entire database will have the same value?
> >
> > Your thoughts would be appreciated.
> >
> > Scott Barnes
> > eCommerce
> > Tourism Queensland / Sunlover Holidays
> > [EMAIL PROTECTED]
> >
> > www.queenslandtravel.com
> > www.sunloverholidays.com
>
> ---
> You are currently subscribed to cfaussie as:
> [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
>
>
>
>
> --------------------
> NOTICE - This communication contains information which is confidential and
> the copyright of Ernst & Young or a third party.
>
> If you are not the intended recipient of this communication please delete
> and destroy all copies and telephone Ernst & Young on 1800 655 717
> immediately. If you are the intended recipient of this communication you
> should not copy, disclose or distribute this communication without the
> authority of Ernst & Young.
>
> Any views expressed in this Communication are those of the individual
> sender, except where the sender specifically states them to be the views of
> Ernst & Young.
>
> Except as required at law, Ernst & Young does not represent, warrant and/or
> guarantee that the integrity of this communication has been maintained nor
> that the communication is free of errors, virus, interception or
> interference.
>
> Liability limited by the Accountants Scheme, approved under the
> Professional Standards Act 1994 (NSW)
> --------------------
>
>
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to [EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
