asked on

Return the ID of a newly inserted record

I need to find out which ID a newly inserted Record has been assigned by the Database Server. Doing this using 'select max(fieldname) from table' after Insert or 'select max(fieldname)+1 from table' before Insert may be too unsecure; I need a 100% working solution, even if others are working on the database. What can I do?

ASKER CERTIFIED SOLUTION

miron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zzconsumer

ASKER

I don't have any experience using Stored Procedures. To be exact, I don't have much experience with SQL Server. Do you think that, if I use transactions from an external application, the result may be apropriate, too? The thing is, as usual my time is VERY limited.

deestuar

If you're using an identity property just return
SELECT @@Identity

BEGIN TRANSACTION

insert into t
(
colunm_1
, ...
)
select
@values_to_insert_1
,

SELECT @error=@@error
IF @error <> 0
BEGIN
ROLLBACK TRANSACTION
SELECT @record_id = 0
END
ELSE
BEGIN
COMMIT TRANSACTION
SELECT @@identity
END
RETURN @error

miron

-I don't have any experience using Stored Procedures.
this is the only way to retrieve it in SQL Server

-To be exact, I don't have much experience with
SQL Server.
this is aprecciated and I tried to power up your app without having you to learn the, material by providing much comments and explanation

-Do you think that, if I use transactions from an external application, the result may be
apropriate, too?
hmm... please, be more specific

-The thing is, as usual my time is VERY limited.
can't help you there

Thanks

protein007

You can do it without stored procedures:

INSERT INTO table (column1, column2, ....) VALUES (value1, value2, ....) SELECT @@IDENTITY

This inserts a new record and returns ID of inserted record.