Return identity id from insert_SQL

Problem/Question/Abstract:

How can i obtain the value of the identity column in a table, when inserting a record with a INSERT SQL statement.

Answer:

Found the following note when surfing several sql-dba website forums:

You can use the SET NOCOUNT statement. SET NOCOUNT ON will prevent SQL Server from telling you how many rows each statement affected. SET NOCOUNT OFF will return SQL Server back to it's default setting. The variable @@ROWCOUNT will always contain the number of rows affected by the previous statement regardless of the setting of NOCOUNT

do use this statement to obtain the value of the inserted id:

whe have a table called TheTable with fields:

Field_ID (identity column)
Field001 (varchar)
Field002 (int)

Set Nocount on
Insert TheTable (Field001, Field002)
VALUES ('ABC', 1)
select IdentityInsert=@@identity
set nocount off

Example usage:

I have an order table with the primary order data, and a related table called orderdetails with the orderdetails per primary order data. The primary order table contains a key, field OrderID, of type identity-column autoincrement start at 0 and increment with 1. The table orderdetails contains the orderId as foreign key.

Case asp e-business website:

The visitor wants to checkout the order wich is composed. Now fire the insert sql to insert a record in the primary order table, using the script in this article to obtain the order id. With the obtained order id fire the several sql_inserts into the the order details table.

Tested with sqlserver 7, ado, d5, asp