Stored Procedure for deleting multiple records from 3 tables

[Deepamathi]

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]

[amitpatel66]

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?

[scripto]

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