1

I have an insert sql with sql server and then call select @@identity straight after, i am trying to use the identity id with a stored procedure, is this even possible

eg

insert into ...
select @@identity

EXEc add 'ss' @@identity

thanks a

edit---

i basically want to use the value of the id which i'm getting now with

SELECT SCOPE_IDENTITY() ;

to use in a query straight after the insert.

Alessandro
  • 305
  • 2
  • 8
  • 22
  • 1
    What is your problem ? Have you noticed that Microsoft indicates that @@identity should be replaced by SCOPE_IDENTITY() ? [http://msdn.microsoft.com/en-us/library/ms187342.aspx](http://msdn.microsoft.com/en-us/library/ms187342.aspx) – Nekresh Feb 07 '11 at 14:07
  • 1
    Related question: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – Oded Feb 07 '11 at 14:10
  • You should be _editing_ your question instead of posting follow on questions in comments, or if a completely new question, post a new question. – Oded Feb 07 '11 at 14:17

2 Answers2

5

Yes, this is possible, though you are probably better off using SCOPE_IDENTITY().

See this SO question about the best way to get the identity of an inserted row.

Community
  • 1
  • 1
Oded
  • 489,969
  • 99
  • 883
  • 1,009
3

Answering the (now deleted) question in the comments...

It is possible to use @@IDENTITY directly in the parameter list of the stored procedure call. For SCOPE_IDENTITY() (which you should be using to avoid problems if a trigger is later added to the table) this syntax is not allowed you need to use an intermediate variable as below.

declare @id int

insert into ...

set @id = SCOPE_IDENTITY() 

EXEC AddEmp2 0,@id
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • thanks, this worked.. i tried this but with select @@identity.. but it didn't work. scope_identity is the way to go. – Alessandro Feb 07 '11 at 14:24
  • @Alessandro - Yes `SELECT @@IDENTITY` wouldn't work you would just use `EXEC AddEmp2 0,@@IDENTITY` (this is of academic interest only though!) – Martin Smith Feb 07 '11 at 14:26