By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Stored Procedure for deleting multiple records from 3 tables

P: 3
Thanks in advance for your help. I need a Stored Procedure that will do the following steps:

1. delete a single record from table GROUP

2. delete all records from table SUBGROUP with a matching 'groupID'
from the deleted GROUP
2. as each record from SUBGROUP is deleted there are multiple associated
records in the table PROFILE with a matching 'subGroupID' that also
need to be deleted


Table definitions: ---------------------------------------------------


CREATE TABLE [dbo].[tblGroup](
[groupID] [int] IDENTITY(100000,1) NOT NULL,
[groupName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]


CREATE TABLE [dbo].[tblSubGroup](
[subGroupID] [int] IDENTITY(100000,1) NOT NULL,
[subGroupName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[groupID] [int] NOT NULL,
[profileID] [int] NOT NULL,
) ON [PRIMARY]


CREATE TABLE [dbo].[tblProfile](
[profileID] [int] IDENTITY(100000,1) NOT NULL,
[profileName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[subGroupID] [int] NOT NULL,
) ON [PRIMARY]
Feb 7 '08 #1
Share this Question
Share on Google+
2 Replies


amitpatel66
Expert 100+
P: 2,367
Thanks in advance for your help. I need a Stored Procedure that will do the following steps:

1. delete a single record from table GROUP

2. delete all records from table SUBGROUP with a matching 'groupID'
from the deleted GROUP
2. as each record from SUBGROUP is deleted there are multiple associated
records in the table PROFILE with a matching 'subGroupID' that also
need to be deleted


Table definitions: ---------------------------------------------------


CREATE TABLE [dbo].[tblGroup](
[groupID] [int] IDENTITY(100000,1) NOT NULL,
[groupName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]


CREATE TABLE [dbo].[tblSubGroup](
[subGroupID] [int] IDENTITY(100000,1) NOT NULL,
[subGroupName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[groupID] [int] NOT NULL,
[profileID] [int] NOT NULL,
) ON [PRIMARY]


CREATE TABLE [dbo].[tblProfile](
[profileID] [int] IDENTITY(100000,1) NOT NULL,
[profileName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[subGroupID] [int] NOT NULL,
) ON [PRIMARY]
Why you need a procedure to do that in case if it is going to be one time process?

And is there any primary key - foreign key relation shp between these tables?
Feb 7 '08 #2

scripto
100+
P: 143
here is a quick solution - we could add error checking between deletes

create procedure dbo.usp_DeleteRecords
@id_to_delete int

as

delete from [dbo].[tblGroup]
where [groupID] = @id_to_delete


delete from [dbo].[tblProfile]
where [subGroupID] in (select [subGroupID] from [dbo].[tblSubGroup]
where [GroupID] = @id_to_delete)

delete from [dbo].[tblSubGroup]
where [GroupID] = @id_to_delete
Feb 7 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.