INSERT - Completly confused - 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)

INSERT - Completly confused

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-05 : 15:16:55

Hello,

I created an INSERT stored procedure and the values are inserted but all in a wrong order. Could someone, please, give it a look.

What I get is:
'en-GB' in dbo.by27_Content [ContentName]
'Test' in dbo.by27_ContentLocalized [ContentCulture]
|- This is 'Test Html' which was truncated because [ContentCulture]
is nvarchar(5)
'Test Name' dbo.by27_ContentLocalized [ContentHtml]

So basically is everything mixed up.
Could someone help me out?

This is my query:
EXECUTE dbo.by27_Content_CreateContent 'en-GB', 'Test Html', 'Test Name'

This is my procedure:

-- Define the procedure parameters
@ContentCulture NVARCHAR(5),
@ContentHtml NVARCHAR(MAX),
@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 Null
IF @ContentId IS NULL
BEGIN

-- Create a new ContentId by defining a new GUID
SET @ContentId = NEWID();

-- Insert new content in by27_Content
INSERT dbo.by27_Content (ContentId, ContentName)
VALUES (@ContentId, @ContentName)

-- Insert new localized content in by27_ContentLocalized
INSERT dbo.by27_ContentLocalized (ContentId, ContentCulture, ContentHtml)
VALUES (@ContentId, @ContentCulture, @ContentHtml)

END
ELSE
BEGIN

-- Check if exists content with given culture
IF EXISTS (SELECT * FROM dbo.by27_ContentLocalized WHERE ContentId = @ContentId AND ContentCulture = @ContentCulture)

-- Update localized content in by27_ContentLocalized
UPDATE dbo.by27_ContentLocalized
SET ContentHtml = @ContentHtml
WHERE ContentId = @ContentId AND ContentCulture = @ContentCulture

ELSE

-- Insert new localized content in by27_ContentLocalized
INSERT dbo.by27_ContentLocalized (ContentId, ContentCulture, ContentHtml)
VALUES (@ContentId, @ContentCulture, @ContentHtml)

END

-- Create procedure
GO

Thanks,
Miguel

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 15:29:54

Try executing like this, then does it work?

EXECUTE dbo.by27_Content_CreateContent @ContentCulture = 'en-GB', @ContentHtml = 'Test Html', @ContentName = 'Test Name'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:39:33

Are you running the SP with the parameters in the right order?

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-05 : 16:07:13

quote:
Originally posted by snSQL

Try executing like this, then does it work?

EXECUTE dbo.by27_Content_CreateContent @ContentCulture = 'en-GB', @ContentHtml = 'Test Html', @ContentName = 'Test Name'

Yep, that works fine

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2006-12-05 : 16:09:10

quote:
Originally posted by Peso

Are you running the SP with the parameters in the right order?

Peter Larsson
Helsingborg, Sweden

Hi,

Yes, I am doing it in the right order and that is why I think it is strange. Now the query is working but I would like to understand why this is happening with my query.

Thanks,
Miguel

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 16:09:47

quote:
Originally posted by shapper

quote:
Originally posted by snSQL

Try executing like this, then does it work?

EXECUTE dbo.by27_Content_CreateContent @ContentCulture = 'en-GB', @ContentHtml = 'Test Html', @ContentName = 'Test Name'

Yep, that works fine

OK, so then when you got the wrong data, either you didn't execute with

EXECUTE dbo.by27_Content_CreateContent 'en-GB', 'Test Html', 'Test Name'

OR your procedure isn't like this

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

AS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources