CODEWITHSUNDEEP
sqlsql-server
CRK
CRK

Reputation: 353

Lock and insert a row in SQLSERVER

I want to insert a row and return the identity column value from a procedure! For this I dont want to use any advanced things like @@identity or SCOPE_IDENTITY etc as I don't have a clear picture about how they work. So I am planning to do this

  1. lock a table
  2. do the insert
  3. return the max of table

I am familiar with oracle lock

lock table tab1 in exclusive mode;
INSERT INTO tab1.....;
select max(id) into retVal from tab1;

Questions:

  1. Is there anything wrong with my approach?
  2. How can i achieve this in SQL SERVER?

Upvotes: 0

Views: 274

Answers (2)

coge.soft
coge.soft

Reputation: 1674

You'll be creating more problems than solving by forcing locks on your database/tables. Let the Database Management System (DBMS) manage the database.

All you need to do is:

BEGIN TRANSACTION --added after original answer
    INSERT INTO tab1....;
    SELECT @@identity AS [last_id] INTO retVal;
END TRANSACTION --added after original answer

I trust you know that you can only SELECT...INTO retVal so long as the retVal table doesn't already exist.

Upvotes: 1

Mathese F
Mathese F

Reputation: 559

which version of sql server it is?

If you are running sql server 2008+ (r2 i thing)

The safest way is to use the output statement of an insert.

Like this :

insert into Customer (Name, Address, Phone) output inserted.CustomerId into @CustomerId values (......)

Upvotes: 2

Related Questions