DELETE Procedure. How to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

DELETE Procedure. How to do this?

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-05 : 13:21:55

Hello,

I created a delete procedure which is working but I still have a
problem.

When I delete a localized content from dbo.by27_ContentLocalized given
a ContentName and ContentCulture I want to check if this is the only
record in ContentLocalized for that ContentName.

If it is then I also want to delete the record in dbo.by27_Content
which has that ContentName.

How can I do this?

Thanks,
Miguel

Here is my DELETE procedure:

-- Define the procedure parameters
@ContentCulture NVARCHAR(5),
@ContentName NVARCHAR(100)

AS

-- Allows @@ROWCOUNT and the return of number of records when
ExecuteNonQuery is used
SET NOCOUNT OFF;

-- Declare and define ContentId
DECLARE @ContentId UNIQUEIDENTIFIER;
SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName =
@ContentName

-- Check if ContentId is Not Null
IF @ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE ContentId = @ContentId

-- Delete content from dbo.by27_Content
DELETE
FROM dbo.by27_Content
WHERE ContentName = @ContentName;

END
ELSE

-- Delete localized content from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture)

END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 14:51:36

I think the piece you want for the delete at the end is this, but your code is confusing because you also have a check for @ContentCulture IS NULL and you didn't say what you want to do in that case.

-- Delete localized content from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture)
IF NOT EXISTS (SELECT * FROM dbo.by27_ContentLocalized WHERE ContentID = @ContentID)
BEGIN
DELETE
FROM dbo.by27_Content
WHERE (ContentID = @ContentID)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources