469,616 Members | 1,741 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

Stored Procedure for deleting multiple records from 3 tables

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
2 8998
amitpatel66
2,367 Expert 2GB
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
143 100+
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.

Similar topics

4 posts views Thread by deprins | last post: by
18 posts views Thread by Jarrod Morrison | last post: by
9 posts views Thread by vikram.mankar | last post: by
4 posts views Thread by yin_n_yang74 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.