Post reply

cursors

  • February 15, 2006 at 2:29 am

    #170688

    hi,

    i am making a global delete procedure where if i delete a record in a table, all the related records in the child/dependent tables must be deleted.

    now the problem......

    as im new to the concept of cursors i need help in using cursors.

    i have a table which lists the dependent table names as foll.

    8 pinqdet   dbo  1

    8 pquotdet dbo  1

    8 sinqdet   dbo  1

    8 sorddet   dbo  1

    8 squotdet dbo  1

    using these details i need to take in account one child table name at time and delete its respective row entry .

    but as im new to cursors i need to know a simple way of accessing the above temp tables contents one row at a time.

    searched a lot but the info i managed to get was too complicated fora bigineer like me.

    can any one help me.....

    Viral

  • February 15, 2006 at 6:15 am

    #620990

    Nooooooooooooo!

    ON DELETE CASCADE

  • February 16, 2006 at 10:51 am

    #621430

    With a little less drama than the previous response, why do you want to do it one row at a time? You're going to slow the delete process WAY down. It would be much faster to construct a comprehensive delete statement with a where clause that catches the set of all rows which need to be deleted. Or, assuming you have built the relations into your database properly, do the:

    ON DELETE CASCADE

    That said, assuming you really want to do it with a cursor. (Again, this is a bad plan, SQL is optimized for set based transactions.) you would create a cursor as such:

    /* Start Code */
-- Create the variables to hold each instance of your data.  Names and types are up to you.
DECLARE @var1 varchar(50)
DECLARE @var2 varchar(50)

/* Declare the cursor.  Replace cur_MyCursor, column1, column2, MyTable, columnX, and 'Constraint' as applicable to your situation. */
DECLARE 
  cur_MyCursor 
CURSOR FOR
  SELECT 
    column1, 
    column2 
  FROM
    MyTable
  WHERE
    columnX = 'Constraint'

-- Open the cursor and get the first result set
OPEN cur_MyCursor
FETCH NEXT FROM cur_MyCursor INTO @var1, @var2

/* Begin looping through your data and slowing your server down */
WHILE @@FETECH_STATUS = 0
BEGIN
  /* insert your custom code here.  @var1 and @var2 contain data from column1 and column2 respectivly */
  FETCH NEXT FROM cur_MyCursor INTO @var1, @var2
END

/* Clean up */
CLOSE cur_MyCursor
DEALLOCATE cur_MyCursor

/* End Code */

    Also, if you are very certain that none of the underlying data will change while this process goes on, you could change the cursor declaration to be a static cursor, which will speed things up a bit, but still not as fast as a set based operation.

    /* More Code */
DECLARE cur_MyCursor CURSOR STATIC FOR
/* The rest is the same */
/* End code */

    There ya go, use or abuse as you will.

  • February 21, 2006 at 4:30 am

    #622214

    "...with a little less drama..."

    Humph - you take all the fun out of being a DBA

Viewing 4 posts - 1 through 3 (of 3 total)

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