Problems with SP and @IDENTITY

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)

Problems with SP and @IDENTITY

Author

Topic

pehim
Starting Member

6 Posts

Posted - 2002-05-30 : 06:10:58

I have problem with at Stored Procedure on SQL Server 2000. I need to get an @@IDENTITY back from a SP.
The SP looks like this:

CREATE PROCEDURE sp_NewCompany
(
@LCID nvarchar (4),
@Email nvarchar (200),
@Password nvarchar (200),
@Name nvarchar (320),
@PostalCode int,
@City nvarchar (100),
@Postbox nvarchar (300),
@Address nvarchar (300),
@CVR nvarchar (40),
@Website nvarchar (100),
@Phone nvarchar (100),
@Fax nvarchar (100),
@Logo nvarchar (200)
)
AS
INSERT INTO Companies (LCID,Email,Password,Name,PostalCode,City,Postbox,Address,CVR,Website,Phone,Fax,Logo)
VALUES (@LCID,@Email,@Password,@Name,@PostalCode,@City,@Postbox,@Address,@CVR,@Website,@Phone,@Fax,@Logo)
RETURN SELECT @@IDENTITY
GO

But, when I run the SP from Query Analyzer I get no return value:

sp_NewCompany 'DK','company@test.dk','secret','Jim Jones',1234,'Copenhagen','264','Street 1','23456789',
'www.test.dk','2828282','2929929','companylogo.gif'

It only says:

(1 row(s) affected)

What am I doing wrong, can anybody help?

Edited by - peh@immisceo.dk on 05/30/2002 07:27:33

MakeYourDaddyProud

184 Posts

Posted - 2002-05-30 : 06:47:18

Try saving @@identity to a local variable first. I suspect that the @@identity is be posted from the result of the return, hence zero. Rather similar to the behaviour of @@error system variable.

pehim
Starting Member

6 Posts

Posted - 2002-05-30 : 07:17:02

Thanx for answering so fast, but could you post some code to do that - I'm kind of new to SQL Server

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-30 : 07:19:47

Try this:

-- Execute the stored procedure and return code values.

DECLARE @ReturnCode INT
EXEC @ReturnCode = sp_NewCompany 'DK','company@test.dk','secret','Jim Jones,1234,'Copenhagen','264','Street 1','23456789',
'www.test.dk','2828282','2929929','companylogo.gif'

pehim
Starting Member

6 Posts

Posted - 2002-05-30 : 07:25:36

That didn't work :-(
Any other suggestions?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-30 : 08:12:38

sp_NewCompany

declare @rc int
insert ...
select @rc = @@identity
return @rc
go

call by
exec @id = sp_NewCompany ...

I prefer to use output parameters though and reserve return codes for error codes.

create procedure sp_NewCompany
@id int output
..
as
insert ...
select @id = @@identity
return
go

call by
exec @rc = sp_NewCompany @id output, ...

If this doesn't work check there isn't something that is losing the identity like a trigger.
Alsl check @@error to make sure the insert works.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 05/30/2002 08:13:25

ToddV
Posting Yak Master

218 Posts

Posted - 2002-05-30 : 08:22:25

I agree with nr about the output parameter, but I will also point out one more thing.

When the original procedure gets to return it does not return, It does not run any of the statements after it.

If you want to return the Identity as a record set, move select @@Identity before the return
SELECT @@IDENTITY
RETURN

To Return @@Identity as a return status, you can simply remove the select (I think).
RETURN @@IDENTITY

I however agree with nr's recommendation to use an output parameter.

pehim
Starting Member

6 Posts

Posted - 2002-05-30 : 08:40:15

Hmm... The Output method doesn't work:

SP:
CREATE PROCEDURE sp_NewCompany
(
@LCID nvarchar (4),
@Email nvarchar(100),
@Password nvarchar (200),
@Name nvarchar (320),
@PostalCode int,
@City nvarchar (100),
@Postbox nvarchar (300),
@Address nvarchar (300),
@CVR nvarchar (40),
@Website nvarchar (100),
@Phone nvarchar (100),
@Fax nvarchar (100),
@Logo nvarchar (200),
@FK_CID int output
)
AS

INSERT INTO Companies (LCID,Email,Password,Name,PostalCode,City,Postbox,Address,CVR,Website,Phone,Fax,Logo)
VALUES (@LCID,@Email,@Password,@Name,@PostalCode,@City,@Postbox,@Address,@CVR,@Website,@Phone,@Fax,@Logo)
SELECT @FK_CID = @@IDENTITY
RETURN
GO

QA:

DECLARE @FK_CID int
exec @FK_CID = sp_NewCompany 'DK','company@test.dk','123','Jim Jones',1234,'Copenhagen','264','Street 1','23456789',
'www.test.dk','2828282','2929929','companylogo.gif',@FK_CID

It returns:
(1 row(s) affected)

However when I use the return method it works fine:
CREATE PROCEDURE sp_NewCompany
(
...
)
AS
INSERT ....
SELECT @@IDENTITY
RETURN

Am I doing something wrong or what???

ToddV
Posting Yak Master

218 Posts

Posted - 2002-05-30 : 08:43:28

Try this with output parameter method.
DECLARE @FK_CID int
exec @FK_CID = sp_NewCompany 'DK','company@test.dk','123','Jim Jones',1234,'Copenhagen','264','Street 1','23456789',
'www.test.dk','2828282','2929929','companylogo.gif',@FK_CID OUTPUT

SELECT "@FK_CID" = @FK_CID

pehim
Starting Member

6 Posts

Posted - 2002-05-30 : 08:57:42

Hmm, that doesn't work either - I guess I stick to the Return method

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-30 : 10:41:53

Should work.
If you get this sort of problem it is always worth writing some test code.
Try this

create procedure spTest
@i int output
as
declare @j int
select @i = @i + 1, @j = @i
return @j
go

create procedure spCall
as
declare @i int, @rc int
set @i = 5
exec @rc = spTest @i output
select @rc, @i
go

exec spCall

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

oob
Starting Member

15 Posts

Posted - 2002-05-30 : 12:09:51

try this

CREATE PROCEDURE sp_NewCompany
(
@NewID int output,
@LCID nvarchar (4),
@Email nvarchar (200),
@Password nvarchar (200),
@Name nvarchar (320),
@PostalCode int,
@City nvarchar (100),
@Postbox nvarchar (300),
@Address nvarchar (300),
@CVR nvarchar (40),
@Website nvarchar (100),
@Phone nvarchar (100),
@Fax nvarchar (100),
@Logo nvarchar (200)
)
AS
INSERT INTO Companies (LCID,Email,Password,Name,PostalCode,City,Postbox,Address,CVR,Website,Phone,Fax,Logo)
VALUES (@LCID,@Email,@Password,@Name,@PostalCode,@City,@Postbox,@Address,@CVR,@Website,@Phone,@Fax,@Logo)

set @NewID =(SELECT @@IDENTITY)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources