How to get the just inserted row in SQL Server stored procedure (without using trigger)?

Question

I have stored procedures that inserts/updates records in some tables. Some columns of those tables have default values or auto-increment. Here's what I have:

ALTER PROCEDURE [dbo].[Usp___NewExpense]
  @iCampaignID int,
  @iCategory int,
  @iUserID int,
  @dDate Date,
  @iAmountInINR int,
  @strComments VarChar(200)
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO dbo.Tbl_Expenses(iCampaignID, iCategory, dDate, iAmountInINR, strComments)
    VALUES (@iCampaignID, @iCategory, @dDate, @iAmountInINR, @strComments);
  -- How to get the record inserted using the above statement here without using triggers
  -- or another select statement, so that I can assign values to the following variables?

  Declare @justInsertedValue1 type1;
  Declare @justInsertedValue2 type2;
  Declare @justInsertedValue3 type3;

  INSERT INTO dbo.Tbl_SomeOtherTable(col1, col2, col3) 
  VALUES (justInsertedValue1, justInsertedValue2, justInsertedValue3);
END
GO

Tbl_Expenses has about 9 columns in which two have default values and two have auto-increment set. How can I get the just inserted record just below my INSERT statement?

I know that I can use SCOPE_IDENTITY() and then a SELECT, but a query would probably make it inefficient (am I right?).

(By getting the just inserted record, I mean values of all fields of the just inserted record)

Edit: I haven't specified values for all the fields in my INSERT statement. I want to get those values inserted automatically by SQL Server due to DEFAULT/AUTO INCREMENT constraints also.

you just inserted that data using variables. Why do you need to fetch the data again? the same data is STILL in those variables. — Marc B, Apr 11 '14 at 14:13
@Marc: I haven't specified values for all the fields in the insert statement. — Sachin Joseph, Apr 11 '14 at 14:16
You should be able to use `OUTPUT` to get the values you are looking for (if your version of SQL Server supports it). — Dave Mason, Apr 11 '14 at 14:18
If you haven't specify the values that columns would be null or default values. — Oasis, Apr 11 '14 at 14:18
You should be able to determine the values anyway. The columns will be null, the default value, or can be gotten by SCOPE_IDENTITY(). No need to select from the table. — Vulcronos, Apr 11 '14 at 14:19
possible duplicate of [How do I use an INSERT statement's OUTPUT clause to get the identity value?](http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) — Shantanu Gupta, Apr 11 '14 at 14:19

score 9 · Accepted Answer · answered Apr 11 '14 at 14:19

9

You can use the OUTPUT clause. You can even combine both inserts into one composite:

create table T1 (ID int IDENTITY(1,1) not null,ColA varchar(10) not null)
create table T2 (ID int IDENTITY(1,1) not null,T1ID int not null,ColB varchar(10) not null)


--Look ma! no local variables at all
insert into T2 (T1ID,ColB)
select t1.ID,'def'
from (
    insert into T1(ColA)
    output inserted.ID
    values ('abc')
    ) t1

select * from T1
select * from T2

Results:

ID          ColA
----------- ----------
1           abc

ID          T1ID        ColB
----------- ----------- ----------
1           1           def

answered Apr 11 '14 at 14:19

Damien_The_Unbeliever

234,701
27
340
448

2

+1 I'm constantly amazed at how few SQL Server developers apparently know about the `OUTPUT` clause ... a true **gem** ! – marc_s Apr 11 '14 at 14:20
2

One to be careful of - depending on the SQL version, the OUTPUT clause doesn't work on a table with triggers, You need to use OUTPUT INTO instead (to a table variable) – Mike Apr 11 '14 at 15:19

How to get the just inserted row in SQL Server stored procedure (without using trigger)?

1 Answers1