Cursors are bad mmmkay. There is pretty much no excuse for ever using cursors in a stored procedure, there is nearly always a better way of writing the query. You do realise when you use a cursor every iteration is like doing another query to the database, i can't begin to tell you how inefficient they are.
But even better yet used derived tables. (see http://www.sql-server-performance.com/jg_derived_tables.asp) http://www.sql-server-performance.com/transact_sql.asp <[EMAIL PROTECTED]> wrote in message news:22145@cfaussie... > > 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/
