Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sparrow37
Posting Yak Master
148 Posts |
Posted - 2009-08-05 : 02:34:33
|
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))ASSET NOCOUNT ONdelete from [WholeSalers]WHERECAST(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
30421 Posts |
Posted - 2009-08-05 : 03:28:48
|
Are you using SQL Server 2008? N 56°04'39.26"E 12°55'05.63" |
|
|
sparrow37
Posting Yak Master
148 Posts |
Posted - 2009-08-05 : 03:42:29
|
SQL server 2005 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-05 : 03:47:44
|
Something like thisALTER PROCEDURE dbo.Delete_WholeSalers( @WholesalerIDs VARCHAR(MAX))ASSET NOCOUNT ONDECLARE @IDs TABLE ( ID INT )DELETEOUTPUT deleted.WholesalerIDINTO @IDsFROM [WholeSalers]WHERE CAST(WholesalerID AS VARCHAR(50)) in (SELECT * FROM fn_SplitToList(@WholesalerIDs))UPDATE wSET w.SomeCol = 0FROM Customers AS wINNER JOIN @IDs AS i ON i.ID = c.WholesalerID N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|