CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Sachin Joseph
Sachin Joseph

Reputation: 19697

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

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.

Upvotes: 2

Views: 3081

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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

Upvotes: 9

Related Questions