•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 428,634 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,010 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 1347 | Replies: 4
 |
•
•
Join Date: Feb 2008
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Hi there.
i would like this procedure to delete all rows in 'sale' where date_modified < (getdate() +0.02) and all the related rows from 'sale_detail' and 'sale_address' i have written the code below, but ti think this might only delete one row from sale and its related rows in the other tables. Is this correct or will it detele all entries from sale etc. If not any suggestions?
Thanks...
i would like this procedure to delete all rows in 'sale' where date_modified < (getdate() +0.02) and all the related rows from 'sale_detail' and 'sale_address' i have written the code below, but ti think this might only delete one row from sale and its related rows in the other tables. Is this correct or will it detele all entries from sale etc. If not any suggestions?
Thanks...
CREATE PROCEDURE Sale_DeleteSaleTimedOut
AS
set nocount on
DECLARE
@sale_id int
select @sale_id = sale_id from sale where date_modified < (getdate() +0.02)
IF (@sale_id is NOT NULL) AND (@sale_id <> '')
BEGIN
delete sale_detail WHERE sale_id = @sale_id
delete sale_address WHERE sale_id = @sale_id
delete sale WHERE sale_id = @sale_id
END
set nocount off
•
•
Join Date: Mar 2008
Location: Holland
Posts: 9
Reputation:
Rep Power: 0
Solved Threads: 1
Newbie Poster
This will only work if it finds on result in the query "select @sale_id = sale_id from sale where date_modified < (getdate() +0.02)
"
It's better to do it with a cursor
"
It's better to do it with a cursor
CREATE PROCEDURE Sale_DeleteSaleTimedOut
AS
set nocount on
DECLARE
@sale_id int
create del_cursor cursor
for
select sale_id from sale where date_modified < (getdate() +0.02) and sale_id <> ''
open del_cursor
fetch next from del_cursor into @sale_id
while @@fetch_status <> 0
BEGIN
delete sale_detail WHERE sale_id = @sale_id
delete sale_address WHERE sale_id = @sale_id
delete sale WHERE sale_id = @sale_id
fetch next from del_cursor into @sale_id
END
close del_cursor
deallocate del_cursor
set nocount off Last edited by peter_budo : Mar 10th, 2008 at 8:26 pm. Reason: Keep It Organized - please use [code] tags
•
•
Join Date: Dec 2007
Location: Raleigh, NC
Posts: 31
Reputation:
Rep Power: 1
Solved Threads: 4
Light Poster
Cursors are slooowwwwwww. you can add the ON DELETE CASCADE option to the foreign key in the sale_detail and sale_address tables. This makes it so that whenever you delete an entry in the parent table, all dependent records are deleted in the child tables that are created with this option. alternatively:
almost anything you can do with cursors you can do with joins or subqueries or temporary tables and all of the above will be faster.
CREATE PROCEDURE Sale_DeleteSaleTimedOut
AS
BEGIN
BEGIN TRANSACTION DeleteSaleTimedOut
DELETE FROM sale_detail
FROM sale_detail AS d
INNER JOIN sale AS s
ON d.sale_id = s.sale_id
WHERE s.date_modified < (getdate() +0.02)
DELETE FROM sale_address
FROM sale_address AS a
INNER JOIN sail AS s
ON a.sale_id = s.sale_ID
WHERE s.date_modified < (getdate() +0.02)
DELETE FROM sale AS s
WHERE s.date_modified < (getdate() +0.02)
COMMIT TRANSACTION DeleteSaleTimedOut
END
almost anything you can do with cursors you can do with joins or subqueries or temporary tables and all of the above will be faster.
Aaron Sterling
•
•
Join Date: Feb 2008
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Hi there
thanks for the reply's I worked it out for myself befor anyone replied using the code below, i'm not sure which method will be the quickest? I'll run some tests on the database to have a look.
thanks for the reply's I worked it out for myself befor anyone replied using the code below, i'm not sure which method will be the quickest? I'll run some tests on the database to have a look.
CREATE PROCEDURE Sale_DeleteSaleTimedOut
AS
set nocount on
DECLARE
@sale_id int
WHILE EXISTS (SELECT sale_id from sale where (date_modified+0.02) < getdate() )
BEGIN
select @sale_id = sale_id from sale where (date_modified+0.02) < getdate()
IF (@sale_id is NOT NULL) AND (@sale_id <> '')
BEGIN
delete sale_detail WHERE sale_id = @sale_id
delete sale_address WHERE sale_id = @sale_id
delete sale WHERE sale_id = @sale_id
End
End
set nocount off |
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
ajax asp database decimal seperator thousand seperator when using sql server deleting records from ms sql table where columns have duplicate values developer development gpt guid partition table hacker management studio 2005 microsoft msdn news office partition security software sql sql cache dependency with polling-based invalidation survey vista
- Previous Thread: How do I Backup the MS SQL server 2000 database file (mdf) to my local PC ?
- Next Thread: (Sum(Sum(MarkU)))/5
