Return identity of row inserted with sql stored procedure

Question

I'm working on a legacy project and need to insert a row and return either that row or it's identity.

the new row is inserted with the correct values, only nothing is returned in the dataset when it gets back to .net.

I've tried selecting @@identity, RETURN, OUTPUT, but with everything i try the dataset is empty (but not null).

It's not the fault of MyUtils.DBHelper.GetDataSet, as this is used in other places and executes and returns ok.

USE [dbname]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[DuplicateOrder]
(
    @sourceid int,
    @var1 int,
    @var2 decimal(10,2),
)
AS

INSERT INTO OrderHeader (
    Users_ID,
    Stores_ID,
    )
SELECT 
    @var1,
    @var2
FROM Order
WHERE id = @sourceid
GO

The code i'm using to execute the stored procedure is:

Using cmd As New SqlCommand("DuplicateOrder") With {.CommandType = CommandType.StoredProcedure}
                    cmd.Parameters.AddWithValue("@sourceid", sourceId)
                    cmd.Parameters.AddWithValue("@var1 ", var1 )
                    cmd.Parameters.AddWithValue("@var2 ", var2 )
                    ds = MyUtils.DBHelper.GetDataSet(cmd)
End Using

How have you tried to select @@identity? Unless you are in a multi connection environment, this work well usually — Grantly, Oct 20 '15 at 09:17

score 1 · Accepted Answer · answered Oct 20 '15 at 09:29

This should do the trick:

ALTER Procedure [dbo].[DuplicateOrder]
(
    @sourceid int,
    @var1 int,
    @var2 decimal(10,2)
)
AS

INSERT INTO OrderHeader (
    Users_ID,
    Stores_ID
    )
SELECT 
    @var1,
    @var2

SELECT @@IDENTITY AS ident
GO

I tested it on SQL server and it returns one row with the column ident.

score 0 · Answer 2 · answered Oct 20 '15 at 09:17

0

have you tried executing this SELECT IDENT_CURRENT ('Table') AS Current_Identity; it should return the last generated identity in the current session and scope

answered Oct 20 '15 at 09:17

hassan arafat

657
5
15

score 0 · Answer 3 · answered Oct 20 '15 at 09:53

@@identity and return wouldn't work (SCOPE_IDENT(), IDENT_CURRENT() as well wouldn't work if you intend to insert multiple rows at once).

Your best bet is to use the OUTPUT but I don't see any OUTPUT in your code. Here is a sample for you (the code part is in C# but you can easily convert it, say using Telerik converter or write yourself - there are N ways to pass the data, I chose XML in this sample):

SQL code to generate sample table and procedure:

USE [Test];
GO

CREATE TABLE IdentityTest
  (
    Id INT IDENTITY
           NOT NULL
           PRIMARY KEY ,
    FirstName VARCHAR(20) ,
    LastName VARCHAR(20)
  );
GO

CREATE PROCEDURE InsertTestData
  (
    @XML VARCHAR(MAX) ,
    @NodeName VARCHAR(1000)
  )
AS
  BEGIN

    DECLARE @myIDTable TABLE ( theID INT );

    DECLARE @hDoc INT;
    DECLARE @tbl TABLE
      (
        fName VARCHAR(20) ,
        lName VARCHAR(20)
      );
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML;
    INSERT  @tbl
    SELECT  *
    FROM    OPENXML(@hDoc, @NodeName, 1) WITH (fName VARCHAR(20), lName VARCHAR(20));
    EXEC sp_xml_removedocument @hDoc;

    INSERT  INTO [IdentityTest] ( [FirstName], [LastName] )
    OUTPUT  [Inserted].[Id]
            INTO @myIDTable
    SELECT  t.[fName], t.[lName]
    FROM    @tbl AS t;

    SELECT  *
    FROM    @myIDTable AS [mit];

  END;
GO

And this is the C# code using the SP and inserting multiple rows and getting back their IDs (might have returned the full row data):

void Main()
{
  List<Person> people = new List<Person> {
    new Person { FName = "Sam", LName="Jones"},
    new Person { FName = "Cetin", LName="Basoz"},
    new Person { FName = "John", LName="Doe"},
    new Person { FName = "Steven", LName="Smith"},
    new Person { FName = "Bob", LName="Carpenter"},
  };

  for (int i = 0; i < 100; i++)
  {
    people.Add(new Person
    {
      FName = string.Format("FName#{0}", i),
      LName = string.Format("LName#{0}", i)
    });
  }

  var peopleAsXML = new XElement("People",
     from p in people
     select new XElement("Person",
       new XAttribute("fName", p.FName),
       new XAttribute("lName", p.LName)));

  string sql = @"InsertTestData";
  DataTable result = new DataTable();
  using (SqlConnection con = new SqlConnection(@"server=.\SQLExpress2012;Trusted_Connection=yes;Database=Test"))
  {
    SqlCommand cmd = new SqlCommand(sql, con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@XML", peopleAsXML.ToString());
    cmd.Parameters.AddWithValue("@NodeName", "/People/Person");
    con.Open();
    result.Load(cmd.ExecuteReader());
    con.Close();
  }
  // check top 5 returned
  for (int i = 0; i < 5; i++)
  {
    Console.WriteLine((int)result.Rows[i]["theID"]);
  }
}

public class Person
{
  public string FName { get; set; }
  public string LName { get; set; }
}

Return identity of row inserted with sql stored procedure

3 Answers3

Linked