Problem Retrieving @@Identity - 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

SQL Server Development (2000)

Problem Retrieving @@Identity

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-02-06 : 14:08:39

I am using calling another SP like this:


--Post ID to be entered into the Broadcast Database.
DECLARE @PostID int
--First create the Post for the user in the appropriate forum by calling the existing SP. 
EXEC @PostID = AspNetForums.dbo.forums_AddPost @ForumID,0,@NewSubject,@UserName,@NewBody,0,null

But I can't seem to fill @PostID with the new value.

Here is the forums_AddPost code at the end where it returns the PostID.


...
COMMIT TRAN
SET NOCOUNT OFF
SELECT PostID = @NewPostID
GO

Alex Polajenko

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 14:18:15

You need to make @PostID an OUTPUT parameter in your stored procedure, then:

DECLARE @PostID INT
EXEC AspNetForums.dbo.forums_AddPost @ForumID,0,@NewSubject,@UserName,@NewBody,0,null,@PostID OUTPUT

Tara

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-02-06 : 14:19:32

quote:
Originally posted by tduggan

You need to make @PostID an OUTPUT parameter in your stored procedure, then:

DECLARE @PostID INT
EXEC AspNetForums.dbo.forums_AddPost @ForumID,0,@NewSubject,@UserName,@NewBody,0,null,@PostID OUTPUT

Tara

Thanks but I really don't want to change the SP because it is another project and that may make a negative impact on the project.

Alex Polajenko

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 14:21:17

Then do this

RETURN @NewPostID

instead of

SELECT PostID = @NewPostID

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 14:24:51

Another option, if you cannot modify the proc is something like the following:


CREATE TABLE #PostValue (PostID int)
INSERT INTO #PostValue EXEC AspNetForums.dbo.forums_AddPost @ForumID,0,@NewSubject,@UserName,@NewBody,0,null

DECLARE @PostID int

SELECT @PostID = PostID 
FROM #PostValue

SELECT @PostID

DROP TABLE #PostValue

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 14:27:39

Jay, he can't change the stored procedure so he can't make @PostID an OUTPUT parameter. The solution would have to return the value as a record set, which would mean changing the stored procedure anyway.

My way requires changing the stored procedure as well, but a very minor change.

Alex, you have to change the stored procedure unless it is already returning the value as a record set.

Tara

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-02-06 : 14:33:54

quote:
Originally posted by ehorn

Another option, if you cannot modify the proc is something like the following:

CREATE TABLE #PostValue (PostID int)
INSERT INTO #PostValue EXEC AspNetForums.dbo.forums_AddPost @ForumID,0,@NewSubject,@UserName,@NewBody,0,null

DECLARE @PostID int

SELECT @PostID = PostID
FROM #PostValue

SELECT @PostID

DROP TABLE #PostValue

tHANSK BRO THIS WORKS WELL.

Alex Polajenko

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 14:33:57

I do not believe He would have to change his proc to use my method:



--The remote proc
CREATE PROC test
AS
SET NOCOUNT ON
DECLARE @var varchar(20)
SELECT @var = 'Hello World'
SET NOCOUNT OFF
SELECT value = @var
GO

--The call to the proc and result store
CREATE TABLE #PostValue (PostID varchar(20))
INSERT INTO #PostValue EXEC test

DECLARE @PostID varchar(20)

SELECT @PostID = PostID 
FROM #PostValue

DROP TABLE #PostValue

SELECT @postid as postid

GO
DROP PROC test

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 14:35:33

Ah, I see it now! Thanks.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 14:37:58

Glad that worked for you Alex.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources