No return value - 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 2005 Forums

Transact-SQL (2005)

No return value

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-18 : 05:59:23

Hi, I'm trying to do this:

@PortalId int,
@ModuleId int,
@Author nvarchar(255),
@Title nvarchar(255),
@SubHead nvarchar(255),
@HeadlineDate datetime,
@Source nvarchar(255),
@Summary ntext,
@Keywords nvarchar(512),
@Article ntext,
@Authed bit,
@Email nvarchar(255),
@AuthorMailLink bit,
@Featured bit,
@NumberOfViews int,
@PublishDate datetime,
@ExpireDate datetime,
@Categories varchar(255)

AS

INSERT INTO
dbo.ZMArticle(
PortalId,
ModuleId,
Author,
Title,
Subhead,
Headlinedate,
Source,
Summary,
Keywords,
Article,
Authed,
Email,
AuthorMailLink,
Featured,
NumberOfViews,
PublishDate,
[ExpireDate],
CreatedDate,
LastmodifiedDate
)
VALUES
(
@PortalId,
@ModuleId,
@Author,
@Title,
@Subhead,
@Headlinedate,
@Source,
@Summary,
@Keywords,
@Article,
@Authed,
@Email,
@AuthorMailLink,
@Featured,
@NumberOfViews,
@PublishDate,
@ExpireDate,
GetDate(),
GetDate()
)

DECLARE @ArticleID INT
SET @ArticleID = @@IDENTITY

INSERT INTO dbo.ZMArticleCategories SELECT @ArticleID, intValue FROM dbo.csvToInt(@Categories)

RETURN @@IDENTITY

But I don't seem to get the articleid back as a return value.
What am I doing wrong?

The secret to creativity is knowing how to hide your sources. (Einstein)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-18 : 06:12:27

How are you calling the SP?

Also, use SCOPE_IDENTITY() instead of @@IDENTITY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 06:13:42

Try using SCOPE_IDENTITY() and put it as statement immediately after INSERT.Move the variable declaration to top before Insert.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-18 : 06:18:12

I now have:

@PortalId int,
@ModuleId int,
@Author nvarchar(255),
@Title nvarchar(255),
@SubHead nvarchar(255),
@HeadlineDate datetime,
@Source nvarchar(255),
@Summary ntext,
@Keywords nvarchar(512),
@Article ntext,
@Authed bit,
@Email nvarchar(255),
@AuthorMailLink bit,
@Featured bit,
@NumberOfViews int,
@PublishDate datetime,
@ExpireDate datetime,
@Categories varchar(255)

AS

DECLARE @ArticleID INT

INSERT INTO
dbo.ZMArticle(
PortalId,
ModuleId,
Author,
Title,
Subhead,
Headlinedate,
Source,
Summary,
Keywords,
Article,
Authed,
Email,
AuthorMailLink,
Featured,
NumberOfViews,
PublishDate,
[ExpireDate],
CreatedDate,
LastmodifiedDate
)
VALUES
(
@PortalId,
@ModuleId,
@Author,
@Title,
@Subhead,
@Headlinedate,
@Source,
@Summary,
@Keywords,
@Article,
@Authed,
@Email,
@AuthorMailLink,
@Featured,
@NumberOfViews,
@PublishDate,
@ExpireDate,
GetDate(),
GetDate()
)
SELECT SCOPE_IDENTITY()

SET @ArticleID = SCOPE_IDENTITY()

INSERT INTO dbo.ZMArticleCategories SELECT @ArticleID, intValue FROM dbo.csvToInt(@Categories)

Still not working.

Im using:

Return CType(DotNetNuke.Data.DataProvider.Instance().ExecuteScalar(_spPrefix & "AddArticle", objArticleInfo.PortalId, objArticleInfo.ModuleId, objArticleInfo.Author, objArticleInfo.Title, objArticleInfo.SubHead, GetNull(objArticleInfo.HeadlineDate), objArticleInfo.Source, objArticleInfo.Summary, objArticleInfo.Keywords, objArticleInfo.Article, objArticleInfo.Authed, objArticleInfo.Email, objArticleInfo.AuthorMailLink, objArticleInfo.Featured, objArticleInfo.NumberOfViews, GetNull(objArticleInfo.PublishDate), GetNull(objArticleInfo.ExpireDate), objArticleInfo.Categories), Integer)

The secret to creativity is knowing how to hide your sources. (Einstein)

matty
Posting Yak Master

161 Posts

Posted - 2007-12-18 : 06:32:52

use RETURN @ArticleID

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-18 : 06:43:41

Still not working.

I get a message saying: Invalid attempt to read when no data is present.

The secret to creativity is knowing how to hide your sources. (Einstein)

matty
Posting Yak Master

161 Posts

Posted - 2007-12-18 : 06:52:05

after the insert there is a statement SELECT SCOPE_IDENTITY()
delete it and try...

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-12-18 : 07:27:31

Like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ZM_Articles_AddArticle]

@PortalId int,
@ModuleId int,
@Author nvarchar(255),
@Title nvarchar(255),
@SubHead nvarchar(255),
@HeadlineDate datetime,
@Source nvarchar(255),
@Summary ntext,
@Keywords nvarchar(512),
@Article ntext,
@Authed bit,
@Email nvarchar(255),
@AuthorMailLink bit,
@Featured bit,
@NumberOfViews int,
@ShowInArchive bit,
@PublishDate datetime,
@ExpireDate datetime,
@Categories varchar(255)

AS

DECLARE @ArticleID INT

INSERT INTO
dbo.ZMArticle(
PortalId,
ModuleId,
Author,
Title,
Subhead,
Headlinedate,
Source,
Summary,
Keywords,
Article,
Authed,
Email,
AuthorMailLink,
Featured,
NumberOfViews,
ShowInArchive,
PublishDate,
[ExpireDate],
CreatedDate,
LastmodifiedDate
)
VALUES
(
@PortalId,
@ModuleId,
@Author,
@Title,
@Subhead,
@Headlinedate,
@Source,
@Summary,
@Keywords,
@Article,
@Authed,
@Email,
@AuthorMailLink,
@Featured,
@NumberOfViews,
@ShowInArchive,
@PublishDate,
@ExpireDate,
GetDate(),
GetDate()
)

SET @ArticleID = SCOPE_IDENTITY()

INSERT INTO dbo.ZMArticleCategories SELECT @ArticleID, intValue FROM dbo.csvToInt(@Categories)

RETURN @ArticleID

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 07:34:02

yup.try with that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources