Help with INSERT Procedure

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:

1SET ANSI_NULLSON2GO3SET QUOTED_IDENTIFIERON4GO5ALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture]6 @.ContentNameNVARCHAR(100),7 @.ContentCultureNVARCHAR(5),8 @.ContentHtmlNVARCHAR(MAX)9AS10BEGIN11 SET NOCOUNT ON;12DECLARE @.ContentIdUNIQUEIDENTIFIER;13SET @.ContentId =NEWID();14INSERT dbo.Content15 (16 ContentName17 )18SELECT19 @.ContentName;20SELECT @.ContentId;21INSERT dbo.ContentLocalized22 (23 ContentId,24 ContentCulture,25 ContentHtml26 )27SELECT28 @.ContentId,29 @.ContentCulture,30 @.ContentHtml;31END32GO333435

Thanks,

Miguel

Hello:

Here is one example involving the uniqueidentifier column:

ALTERPROCEDURE [dbo].[test_sp]

@.Captionnvarchar(50),

@.IsPublic

bit

AS

INSERTINTO [Albums]([c_id], [Caption],[IsPublic])VALUES(NEWID(), @.Caption, @.IsPublic)

RETURN

--Thid is the table I copied for you to test:

CREATE

TABLE [dbo].[Albums](

[AlbumID] [int]

IDENTITY(1,1)NOTNULL,

[Caption] [nvarchar]

(50)NOTNULL,

[IsPublic] [bit]

NOTNULL,

[c_id] [uniqueidentifier]

NULL)|||

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture] @.ContentNameNVARCHAR(100), @.ContentCultureNVARCHAR(5), @.ContentHtmlNVARCHAR(MAX)ASBEGINSET NOCOUNT ON;DECLARE @.ContentIdUNIQUEIDENTIFIER,@.ExistingContentNameNVARCHAR(100)SELECT @.ExistingContentName = ContentNameFROM ContentWHERE ContentName = @.ContentName;IF (@.ExistingContentName =null)BEGININSERT INTO dbo.Content (ContentId, ContentName)VALUES (NEWID(), @.ContentName);ENDENDGO

Its not yet complete but i think its a start, does anyone knows how to make a statement that will return FALSE if the select statement returned no data and TRUE if there is >= 1?