Professional Web Applications Themes

Do looping on Stored procedure - Microsoft SQL / MS SQL Server

There are various ways to go about this. 1. create a cursor and loop through each fileid 2. use while loop 3. create tmp table for all fileids and delete as set based - this would be the *best* method. Here is a quick example... e.g. create table #deletefileids(fileid int) insert #deletefileids select 1 union all select 2 union all select 3 update [agent headers] set [file id]=-1 where exists(select * from #deletefileids d where d.fileid= [agent headers].[file id]) delete [file headers] where exists(select * from #deletefileids d where d.fileid= [file headers].[file id]) delete [Home Office Totals] where exists(select * ...

  1. #1

    Default Re: Do looping on Stored procedure

    There are various ways to go about this.
    1. create a cursor and loop through each fileid
    2. use while loop
    3. create tmp table for all fileids and delete as set based - this would be
    the *best* method.

    Here is a quick example...

    e.g.
    create table #deletefileids(fileid int)
    insert #deletefileids
    select 1
    union all select 2
    union all select 3

    update [agent headers]
    set [file id]=-1
    where exists(select * from #deletefileids d where d.fileid= [agent
    headers].[file id])

    delete [file headers] where exists(select * from #deletefileids d where
    d.fileid= [file headers].[file id])
    delete [Home Office Totals] where exists(select * from #deletefileids d
    where d.fileid= [Home Office Totals].[file id])
    ....

    delete from [Itinerary Records]
    where [transaction id] in
    (select [transaction id] from [Transaction Records] where
    exists(select * from #deletefileids d where d.fileid= [Transaction
    Records].[file id])
    ....

    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "Ken" <ken88hotmail.com> wrote in message
    news:02d801c33ea4$741b4a10$a301280aphx.gbl...
    > Hi All
    >
    > I've got a stored procedure which I use to delete records
    > based on fileid. But now i need to do it more than one
    > fileids. Anyone knows how to do looping on stored
    > procedure so I dont need to change the fileid manually?
    > Thanks
    >
    > Here is the code:
    >
    > --!!!!!!!!!!!!!!!!! Change File id
    > here !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    > declare FileID as numeric
    > set FileID=3
    >
    > update [agent headers] set [file id]=-1 where [file id]
    > =FileID
    > delete [file headers] where [file id]=FileID
    > delete [Home Office Totals] where [file id]=FileID
    > delete [files and agents] where [file id]=FileID
    > delete [agent totals] where [file id]=FileID
    >
    > delete from [Itinerary Records]
    > where [transaction id] in
    > (select [transaction id] from [Transaction Records] where
    > [file id]=FileID)
    >
    > delete from [Payment Records]
    > where [transaction id] in
    > (select [transaction id] from [Transaction Records] where
    > [file id]=FileID)
    >
    > delete from [Tax Breakdown Records]
    > where [transaction id] in
    > (select [transaction id] from [Transaction Records] where
    > [file id]=FileID)
    >
    > delete [Transaction Records] where [file id]=FileID
    >
    > delete from [Holder Itinerary Records]
    > delete from [Holder Payment Records]
    > delete from [Holder Tax Breakdown Records]
    >
    > Rgds
    > Ken

    oj Guest

  2. #2

    Default Re: Do looping on Stored procedure

    Can you declare FOREIGN KEYs between the tables with [transaction id]? If
    you create FK constraints with the ON DELETE CASCADE option then the child
    rows are deleted when you delete the parent rows.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Ken" <ken88hotmail.com> wrote in message
    news:02d801c33ea4$741b4a10$a301280aphx.gbl...
    > Hi All
    >
    > I've got a stored procedure which I use to delete records
    > based on fileid. But now i need to do it more than one
    > fileids. Anyone knows how to do looping on stored
    > procedure so I dont need to change the fileid manually?
    > Thanks
    >
    > Here is the code:
    >
    > --!!!!!!!!!!!!!!!!! Change File id
    > here !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    > declare FileID as numeric
    > set FileID=3
    >
    > update [agent headers] set [file id]=-1 where [file id]
    > =FileID
    > delete [file headers] where [file id]=FileID
    > delete [Home Office Totals] where [file id]=FileID
    > delete [files and agents] where [file id]=FileID
    > delete [agent totals] where [file id]=FileID
    >
    > delete from [Itinerary Records]
    > where [transaction id] in
    > (select [transaction id] from [Transaction Records] where
    > [file id]=FileID)
    >
    > delete from [Payment Records]
    > where [transaction id] in
    > (select [transaction id] from [Transaction Records] where
    > [file id]=FileID)
    >
    > delete from [Tax Breakdown Records]
    > where [transaction id] in
    > (select [transaction id] from [Transaction Records] where
    > [file id]=FileID)
    >
    > delete [Transaction Records] where [file id]=FileID
    >
    > delete from [Holder Itinerary Records]
    > delete from [Holder Payment Records]
    > delete from [Holder Tax Breakdown Records]
    >
    > Rgds
    > Ken

    David Portas Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  3. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  4. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM
  5. Replies: 1
    Last Post: November 29th, 07:17 PM

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