Insert using @@identity 3

[TommyTea]

I am trying to insert a record into a sql server table with an identity field as it's key. I am using an ms access pass-through. My table looks like:

review_id int 4 0 <-- identity
reviewer int 4 0
reviewed int 4 0
role_id int 4 0
project_id int 4 0
Reviewer_id nvarchar24 1

my procedure looks like:
CREATE PROCEDURE ud_insert_review_instance
-- Insert a new review instance record
@reviewer int,
@reviewed int,
@role int,
@project int,
@reviewer_id nvarchar(24),
@intIdentity int

AS
Set Nocount on

Declare @intErrorCode int
Select @intErrorCode = @@Error

If @intErrorCode = 0
Begin
Insert into Review_instance (reviewer,reviewed,role_id, project_id, reviewer_id)
Values(@reviewer, @reviewed, @role, @project, @reviewer_id)

Please - what am i doing wrong?
Select @intErrorCode = @@Error,
@intIdentity = @@identity

End

Return @intErrorCode

 

[SQLSister]

If Reviewer ID is an identity field you should not use it in your insert statement.

 

[Veep]

CREATE PROCEDURE ud_insert_review_instance
-- Insert a new review instance record
@reviewer int,
@reviewed int,
@role int,
@project int,
@reviewer_id nvarchar(24),
@intIdentity int OUTPUT

I think that it's looking for @intIdentity as an input parameter. Change it to Output.

 

[TommyTea]

I added the output parameter and entered the following query:
EXEC ud_insert_review_instance 99,12,6,1,'tt'

It still gave me the error:
Server: Msg 201, Level 16, State 4, Procedure

ud_insert_review_instance, Line 0
Procedure 'ud_insert_review_instance' expects parameter '@intIdentity', which was not supplied.

Any more thoughts?

 

[mkal]

Try running this script

Create table Review
(review_id int Identity(1,1),
reviewer int,
reviewed int,
role_id int,
project_id int,
reviewer_id nvarchar(24))
GO
ALTER PROCEDURE ud_insert_review_instance
-- Insert a new review instance record
@reviewer int,
@reviewed int,
@role int,
@project int,
@reviewer_id nvarchar(24),
@intIdentity int OUTPUT

AS
Set Nocount on

Declare @intErrorCode int
Select @intErrorCode = @@Error

If @intErrorCode = 0
Begin
Insert Review (reviewer,reviewed,role_id, project_id, reviewer_id)
Values(@reviewer, @reviewed, @role, @project, @reviewer_id)


Select @intIdentity = @@identity

End

Return @intErrorCode
GO
Declare @ident int
exec ud_insert_review_instance 99,12,6,1,'tt', @ident output
Print str(@ident)

 

[Veep]

If you call it like this it should work also:
EXEC ud_insert_review_instance 99,12,6,1,'tt',@intIdentity=NULL

 

[SQLSister]

Try this: I remove the identity field form your insert and changed @@identity to scopr_identity becasue @@identity will not reliably give you the identity field you just inserted. If there is a trigger onthe table which inserts into another table with and identity field you would get that identity instead with @@identity.

I also added a select statement at the bottom. With that you do not need an output parameter at all as it will return the result of the select statement.
It is possible that you may need to define a default variable for your output variable in order to use it. I never use them so I don't know, but I think It has come up before. If you don;t want to use the output variable and use the select statement instead, then you will need to declare the variable you are storing the identity value in as part of the stored procedure.

ALTER PROCEDURE ud_insert_review_instance
-- Insert a new review instance record
@reviewer int,
@reviewed int,
@role int,
@project int,
@reviewer_id nvarchar(24),
@intIdentity int OUTPUT

AS
Set Nocount on

Declare @intErrorCode int
Select @intErrorCode = @@Error

If @intErrorCode = 0
Begin
Insert Review (reviewer,reviewed,role_id, project_id)
Values(@reviewer, @reviewed, @role, @project)


Select @intIdentity = scope_identity
Select @intIdentity
End

Return @intErrorCode
GO

 

[TommyTea]

It works! Thank you all so much. What happened? I created this database by importing tables from another database. I checked everything to make sure that everything was the same. Everything except the identity setting on the review_id field. I'm sorry for the bother but am delighted with what you all showed me about identity fields.

 

[mkal]

I think you are looking at the wrong field SQLSister, you removed the reviewer_id from the insert but this is not the identity field (well at least not in my table) in my table the identity is defined on the review_id.

You are correct about using the scope_identity, any action that inserts on the table will return the @@Identity and it may not be the proc. Nice call.

 

[SQLSister]

You are right i looked at the wrong field, probably becasue the names were simlliar and iwas reading fast. Oops.