Help with INSERT Procedure - SQL Server Forums

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)

Help with INSERT Procedure

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-11-30 : 10:21:50

Hello,

I have a procedure which INSERTS a new record in two tables [Content] and [ContentLocalized] given [ContentName] and [ContentCulture].

Here are the table structures:

<Content>
|----- [ContentId] Type=UniqueIdentifier PK
| [ContentName] Type=NVarChar(100)
|
| <ContentLocalized>
| [ContentLocalizedId] Type=UniqueIdentifier PK
| -------> [ContentId] Type=UniqueIdentifier FK
| [ContentCulture] Type=NVarChar(5)
| [ContentHtml] Type=NVarChar(MAX)

WHAT I AM MISSING:

> If in <Content> THERE IS a record with the same [ContentName] then this record will be used AND:

If in <ContentLocalized> for the given [ContentName] THERE IS NO such [ContentCulture] then a new will be created with [ContentCulture] and [ContentHtml]

If in <ContentLocalized> for the given [ContentName] THERE IS such [ContentCulture] then its [ContentHtml] will be replaced by the given [ContentHtml]

> If in <Content> THERE IS NOT a record with the same [ContentName] then:

A new <Content> record will be created with [ContentName] and a new <ContentLocalized> record will be created with [ContentCulture] and [ContentHtml].

I know I didn't get there yet.

Could somebody help em out?

I am posting the INSERT Store Procedure as I have now:

1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 ALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture]
6 @ContentName NVARCHAR(100),
7 @ContentCulture NVARCHAR(5),
8 @ContentHtml NVARCHAR(MAX)
9 AS
10 BEGIN
11 SET NOCOUNT ON;
12 DECLARE @ContentId UNIQUEIDENTIFIER;
13 SET @ContentId = NEWID();
14 INSERT dbo.Content
15 (
16 ContentName
17 )
18 SELECT
19 @ContentName;
20 SELECT @ContentId;
21 INSERT dbo.ContentLocalized
22 (
23 ContentId,
24 ContentCulture,
25 ContentHtml
26 )
27 SELECT
28 @ContentId,
29 @ContentCulture,
30 @ContentHtml;
31 END
32 GO
33
34
35

Thanks,

Miguel

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 11:31:54

I think should do it

CREATE PROCEDURE [dbo].[Content_CreateContentByNameAndCulture]
@ContentName NVARCHAR(100),
@ContentCulture NVARCHAR(5),
@ContentHtml NVARCHAR(MAX)
AS
SET NOCOUNT ON;
DECLARE @ContentId UNIQUEIDENTIFIER;
SELECT @ContentId = ContentId FROM dbo.Content WHERE ContentName = @ContentName
IF @ContentId IS NULL
BEGIN
	SET @ContentId = NEWID();
	INSERT dbo.Content (ContentId, ContentName) 
		VALUES (@ContentId, @ContentName)
	INSERT dbo.ContentLocalized (ContentId, ContentCulture, ContentHtml) 
		VALUES (@ContentId, @ContentCulture, @ContentHtml)
END
ELSE
BEGIN
	IF EXISTS (SELECT * FROM dbo.ContentLocalized WHERE ContentId = @ContentId AND ContentCulture = @ContentCulture)
		UPDATE dbo.ContentLocalized 
		SET ContentHtml = @ContentHtml
		WHERE ContentId = @ContentId AND ContentCulture = @ContentCulture
	ELSE
		INSERT dbo.ContentLocalized (ContentId, ContentCulture, ContentHtml) 
			VALUES (@ContentId, @ContentCulture, @ContentHtml)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources