Database delete record using store procedure

[lehuong]

Hi all,
I have a question regards to store procedure in SQL to delete records. Here is my code:


CREATE PROCEDURE dbo.deleteCustomer_Records
@CustomerID INT
AS
BEGIN
Delete from OrderDetail AS A INNER JOIN OrderID B ON A.OrderID = B.OrderID
WHERE
B.CustomerID= @CustomerID
END
GO

I have 3 tables, table Customer with columns: CustomerID, CustomerName... table Order with columns: OrderID, CustomerID, OrderName.. and my OrderDetail with columns: OrderDetailID,OrderID, OrderDetailName... I want to delete all records on OrderDetail tables belong to that customer when I have the CustomerID. I tried to build a delete function in store produre with the INNER JOIN feature.

BUT IT DOES NOT WORK! ANYONE HAVE ANY IDEA FOR THIS STORE PROCEDURE???

Thank you,
lehuong

 

[aftertaf]

does it wokr that way in query analyser??

if not, look into cursors.....

 

[lehuong]

Hi Afteraf,
No it is not working in query analyser. By the way, what do you mean cursor?
Thanks,

 

[Modica82]

I i am understanding you correctly you want to delete from one table and have it delete from the others. Well why dont you just set up a relationship between the tables (CustomerID) and set up the Cascade Delete, so when you delete from the primary table(Customers) it will delete all the records in the corresponding tables(Customer Orders) then all you have to do is create one delete SQL statement that deletes the from the customer table, the DB does the rest.

Delete FROM tblCustomers
Where CustomerID = @CustomerID

Hope this helps,

Rob

 

[AtomicChip]

Modica82's solution is definitely the way to go (just my two cents )

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy