[dba-SQLServer] Stored Procedures - Returning A Value

On 27 May 2005 at 11:50, Paul Hartland wrote:

> 
> Works fine, but I have a field in tblUserLog called LogonID which is an
> autonumber and I would like to return the value to a variable in Visual
> Basic 6 after the insert takes place.  
> 
> Anyone any ideas ow I would go about this ?
> 

>From BOL:

@@IDENTITY (T-SQL)
Returns the last-inserted identity value. 

Syntax
@@IDENTITY

Return Types
numeric

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY 
contains the last identity value generated by the statement. If the 
statement did not affect any tables with identity columns, @@IDENTITY 
returns NULL. If multiple rows are inserted, generating multiple identity 
values, @@IDENTITY returns the last identity value generated. If the 
statement fires one or more triggers that perform inserts that generate 
identity values, calling @@IDENTITY immediately after the statement returns 
the last identity value generated by the triggers. The @@IDENTITY value 
does not revert to a previous setting if the INSERT or SELECT INTO 
statement or bulk copy fails, or if the transaction is rolled back.

Examples
This example inserts a row into a table with an identity column and uses 
@@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)

VALUES ('Accountant',12,125)

SELECT @@IDENTITY AS 'Identity'

  
-- 
Stuart