Stored Procedure to return IDENTITY field 3

[StevenK]

I have not really made use of stored procedures in the past but now find myself needing to.
I have a table TABLE1 which has an IDENTITY field as the PRIMARY KEY. The table structure is as follows :
IDX int IDENTITY,
Field1 char(10),
Field2 int,
Field3 char(20)

I need my stored procedure to INSERT a new entry into the TABLE1 (passing it the values for Field1, Field2 + Field3) but need it to return the newly utilised IDX column. This value for IDX needs to be made use of when INSERT'ing into my next table.

How can this be done ?

Any help would be appreciated.
Thanks in advance.

 

[rt63]

In most normal situations, inserts into table with identity column results in an identity value for the column which is more than the previous maximum by an increment as defined while defining the table.

So if your table does not have any inserts where you plan to suppress auto increment of identity column, a simple way of returning the newly inserted record's identity column value is


select max(IDX) from TABLE1


You can get more info in T-SQL help under IDENTITY keyword. Hope it helps

RT

 

[StevenK]

We've made use of the MAX(IDX) in the past but I'm trying a new approach such that the stored procedure is passed the parameters to be used in the INSERT statement and returns the parameter that is the newly used IDX (IDENTITY) field.

This needs to be within the scope of one stored procedure such that we do not face a problem whereby an entry is INSERT'ed, we then run a MAX(IDX) but in the mean time a further entry has been added and we retrieve the wrongly used IDX value.

Can anyone help with this ?

Steve

 

[Jocsta]

Look up SCOPE_IDENTITY in BooksOnline, this seems to be what you are looking for.
<< JOC >>

 

[cdukes]

Try returning @@Identity

SELECT @retVal = @@IDENTITY

RETURN @RetVal

Hope this helps,

Chris Dukes

 

[StevenK]

Following the advice above I've made use of both @@IDENTITY and/or SCOPE_IDENTITY().
Both seem to work OK.
Is there a favoured way of doing this ?
Are there any pitfalls with either of these methods ?

thanks again
Steve