[dba-SQLServer] Stored Procedures - Returning A Value

Thanks, will try that in a minute





Message date : May 27 2005, 12:27 PM
>From : "Stuart McLachlan" 
To : dba-sqlserver at databaseadvisors.com
Copy to : 
Subject : Re: [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


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm