Entry

How can I request the unique id key that SQL Server generates as it adds the new record?

May 27th, 2000 23:54
unknown unknown, Bruce Anwyl

You can do it if your insert is done in a stored procedure as shown in 
the following code extract (bits removed for clarity so it does not run 
as is).

The final select statement causes the stored procedure to return the
identity value. Call the stored procedure from your page so that the 
results are returned in a recordset.

A big warning on this technique! The statement "SELECT @vintAddressID =
@@Identity" returns the most recent identity value generated by the SQL
Server database. If your insert fires a trigger that inserts a record 
into a table with an identity column then that is the identity value 
that will be returned. Not the one you wanted.

CREATE PROCEDURE SaveAddress(
    @vintAddressID              int = 0,
    @vstrAddressLine1           varchar(100) = null,
    @vstrAddressLine2           varchar(100) = null,
    @vstrAddressLine3           varchar(100) = null,
    @vstrSuburb                 varchar(100) = null,
    @vstrState                  varchar(50) = null,
    @vstrPostCode               varchar(50) = null,
    @vstrCountry                varchar(50) = null,
    @vstrUser                   varchar(200)) AS

INSERT INTO Address
    Values
    (
        @vstrAddressLine1,
        @vstrAddressLine2,
        @vstrAddressLine3,
        @vstrSuburb,
        @vstrState,
        @vstrPostCode,
        @vstrCountry,
        Getdate(),
        @intUserID,
        Getdate(),
        @intUserID
    )

    SELECT @vintAddressID = @@Identity

    SELECT @vintAddressID AS AddressID
End