newbie needs help with @@identity

I have a form that submits to multiple tables. After insertion into the first table I need to access the identity key from the record and use is to associate a record in another table. The form element I'm inserting into the second table however, is not a required field so I think I need to check IS NOT NULL first. In my code below I have copied the insert statement for the first table and the conditional and subsequent insert into the 2nd table. I am uncertain where and how I get and use @.@.identity. The error I'm getting when I run the Check Syntax button is: 'incorrect syntax near @.@.identity.'

I appreciate someone telling me how to correct my syntax.

INSERT INTO GPRA_Activities
(
SubmitDate,
StaffId,
GPRAId,
FreedomID,
DocumentDesc,
ActivityTitle,
ActivityDesc

)
VALUES
(
getDate(),
@.StaffId,
@.GPRAId,
@.FreedomID,
@.DocumentDesc,
@.ActivityTitle,
@.ActivityDesc

SELECT @.@.identity
)

if @.KeywordId1 IS NOT NULL

@.@.identity smallint,

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.@.identity,
@.KeywordId1
)

GO

You need to get the value of @.@.IDENTITY Into a local variable and use it. You cannot use the @.@.IDENTITY by itself.

Declare @.valintINSERT INTO GPRA_Activities(SubmitDate,StaffId,GPRAId,FreedomID,DocumentDesc,ActivityTitle,ActivityDesc)VALUES (getDate(),@.StaffId,@.GPRAId,@.FreedomID,@.DocumentDesc,@.ActivityTitle,@.ActivityDesc)SELECT @.val = SCOPE_IDENTITY()if @.KeywordId1ISNOT NULL-- @.@.identity smallint, I dont know what you are trying to do hereINSERT INTO GPRA_KeywordsUsed ( ActivityId, KeywordId )VALUES ( @.val, @.KeywordId1 )GO

|||

I finally got the SQL code below not to error (though I haven't been able to submit my form yet. Keep getting error message about expected number of parameters. That one will be my nemesis.

What is the difference between @.@.identy and SCOPE_IDENTITY?

DECLARE
@.ActivityId smallint
SELECT @.ActivityId = @.@.Identity

if @.KeywordId1 IS NOT NULL

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.ActivityId,
@.KeywordId1
)

|||

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

I'm new at this so please forgive my ignorance.

So, if I use @.@.identity and there are multiple users of the application at once, could the wrong identity get "grabbed"?

|||

Possible. HEre's some info from Books on line:

For example, there are two tables,T1 andT2, and an INSERT trigger is defined onT1. When a row is inserted toT1, the trigger fires and inserts a row inT2. This scenario illustrates two scopes: the insert onT1, and the insert onT2 by the trigger.

Assuming that bothT1 andT2 have identity columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement onT1. @.@.IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted inT2. SCOPE_IDENTITY() will return the IDENTITY value inserted inT1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

|||thank you. I'll change it.