SP to delete data from two tables

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.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SP to delete data from two tables

Author

Topic

ANGSHU
Starting Member

4 Posts

Posted - 2011-07-20 : 22:11:08

i have the following two table ....there is a relation between the two tables...[userID] is the primary key of tblUser and [userID] is the FK of tblAlbum......

now i need to delete data from tblUser and automatically delete all the corresponding data of tblAlbum

i want to delete from both the tables with one store procedure.......

so friends please help me.......

1st Table

USE [TEST]
GO

/****** Object: Table [dbo].[tblAlbum] Script Date: 07/21/2011 07:35:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAlbum](
[AlbumID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[AlbumName] [nvarchar](50) NULL,
[AlbumPicture] [image] NULL,
CONSTRAINT [PK_tblAlbum] PRIMARY KEY CLUSTERED
(
[AlbumID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblAlbum] WITH CHECK ADD CONSTRAINT [FK_tblAlbum_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO

ALTER TABLE [dbo].[tblAlbum] CHECK CONSTRAINT [FK_tblAlbum_tblUser]
GO

2nd Table

USE [TEST]
GO

/****** Object: Table [dbo].[tblUser] Script Date: 07/21/2011 07:35:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUser](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nchar](10) NULL,
[UserEmail] [nvarchar](50) NULL,
CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblUser] WITH CHECK ADD CONSTRAINT [FK_tblUser_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([UserID])
GO

ALTER TABLE [dbo].[tblUser] CHECK CONSTRAINT [FK_tblUser_tblUser]
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-20 : 22:32:22

[code]
DELETE tblAlbum WHERE UserID = @UserID
DELETE tblUser WHERE UserID = @UserID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ANGSHU
Starting Member

4 Posts

Posted - 2011-07-21 : 22:42:45

@khtan .......im using the same .....now my question is .......can i use this procedure when im working on a professional site

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-21 : 22:44:59

What do you mean by "professional site"? There is nothing that khtan posted that can't be used on any site.

I personally prefer to delete data from child tables by specifying the cascade option in the foreign key constraint.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources