title: How to get the just inserted row in SQL Server stored procedure (without using trigger)?
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><code>Tbl_Expenses</code> 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 <code>INSERT</code> statement?</p>
  
  <p>I know that I can use <code>SCOPE_IDENTITY()</code> and then a <code>SELECT</code>, but a query would probably make it inefficient (am I right?).</p>
  
  <p>(By getting the just inserted record, I mean values of all fields of the just inserted record)</p>
  
  <p><strong>Edit</strong>: I haven't specified values for all the fields in my <code>INSERT</code> statement. I want to get those values inserted automatically by SQL Server due to DEFAULT/AUTO INCREMENT constraints also.</p>