SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update parent table as child is deleted
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sparrow37
Posting Yak Master

147 Posts

Posted - 08/05/2009 :  02:34:33  Show Profile  Reply with Quote
Hi all,

I have two tables. One is users and others is wholesalers. I have created a stored procedure which takes a comma seperated list of wholesaleid and deletes records in wholesale. user table has userid as primary key which is also a foreign key in wholesaler table. I want to update user record when its wholesaler record is deleted.
wholesale and user table has one to one relation with user table as primary table.

Following is my sp:

ALTER PROCEDURE [dbo].[Delete_WholeSalers] (
@WholesalerIDs VARCHAR(MAX)
)

AS

SET NOCOUNT ON


delete from [WholeSalers]
WHERE
CAST(WholesalerID AS VARCHAR(50)) in(select * from [fn_SplitToList]
(@WholesalerIDs))

--update users table based on userid in wholesale table


Please help me on this.

Regards,
Asif Hameed

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/05/2009 :  03:28:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you using SQL Server 2008?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

sparrow37
Posting Yak Master

147 Posts

Posted - 08/05/2009 :  03:42:29  Show Profile  Reply with Quote
SQL server 2005
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/05/2009 :  03:47:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Something like this
ALTER PROCEDURE dbo.Delete_WholeSalers
(
	@WholesalerIDs VARCHAR(MAX)
)
AS

SET NOCOUNT ON

DECLARE	@IDs TABLE
	(
		ID INT
	)

DELETE
OUTPUT	deleted.WholesalerID
INTO	@IDs
FROM	[WholeSalers]
WHERE	CAST(WholesalerID AS VARCHAR(50)) in (SELECT * FROM fn_SplitToList(@WholesalerIDs))

UPDATE		w
SET		w.SomeCol = 0
FROM		Customers AS w
INNER JOIN	@IDs AS i ON i.ID = c.WholesalerID



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000