SQL Server - Return Value After Insert

SQL Server - Return value after INSERT

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

Sql Server return the value of identity column after insert statement

Insert into TBL (Name, UserName, Password) Output Inserted.IdentityColumnName
 Values ('example', 'example', 'example')

INSERT return value of stored procedure

Here is your code with the issues fixed and some recommended improvements. The comments explain what and why.

SP:

CREATE PROCEDURE PERSON.NewPerson
(
  -- Use an output parameter to get values out of an SP
  @NewId INT OUT
)
AS 
BEGIN 
  -- Recommended to always list the columns you are inserting to
  -- Personally my preference is to select them (because that scales to multiple inserts), I never use the 'values' clause.
  INSERT INTO PERSON.ID_PERSON (uid)
    SELECT NEWID();

  SET @NewId = SCOPE_IDENTITY();

  -- The return statement is for a status for the SP, usually 0 for success, some other int for an error
  RETURN 0;
END
GO

Calling SP:

DECLARE @MyNewId INT;

-- Run the SP before your insert to get your new value    
EXEC PERSON.NewPerson @MyNewId OUT;

-- Then insert - ideally with a list of columns
INSERT INTO PERSON.EMPLOYEE
  SELECT @MyNewId, 1, '15434235', '10768348153', '1962-3-2', '1999-10-2', 'PETER', '', 'SMITH', 'HAMMER'

How to get Identity value from SQL server after insert record

Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:

Replace the last concatenation with:

SQLString += "; SELECT SCOPE_IDENTITY();"

Then to retrieve the ID:

int ID = Convert.ToInt32(command.ExecuteScalar());

Returning a value from an INSERT statement in SQL Server 2008

Yes - you can use the little known and little used OUTPUT clause in your INSERT statement

INSERT INTO dbo.YourTable(col1, col2, col3, ...., ColN)
OUTPUT Inserted.Col1, Inserted.Col5, Inserted.ColN
VALUES(val1, val2, val3, ....., valN)

This returns a normal set of data, that you can deal with as you need to.

As the MSDN docs show, you can also send the OUTPUT values into e.g. a table variable or temp table for later use, if you need to.

To answer your updated question, use this:

INSERT INTO dbo.EMPDETAILS(EmpName)
OUTPUT Inserted.EmpID
VALUES("John")

Return value from SQL Server Insert command using c#

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

SQL Server - Best way to get identity of inserted row?

• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

• SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

• IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."

• The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

Return id after insert C# using SQL Server

Try this:

public int CreateAlbum(string _titel, string _name, string _thumb, int _userid)
{
    // define return value - newly inserted ID
    int returnValue = -1;

    // define query to be executed
    string query = @"INSERT INTO tblFotoalbum (fldAlbumHead, fldAlbumName, fldAlbumThumb, fldUserID_FK)
                              VALUES (@titel, @name, @thumb, @userid);
                     SELECT SCOPE_IDENTITY();"

    // set up SqlCommand in a using block   
    using (objCMD = new SqlCommand(query, connection)) 
    {
        // add parameters using regular ".Add()" method 
        objCMD.Parameters.Add("@titel", SqlDbType.VarChar, 50).Value = _titel;
        objCMD.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = _name;
        objCMD.Parameters.Add("@thumb", SqlDbType.VarChar, 100).Value = _thumb;
        objCMD.Parameters.Add("@userid", SqlDbType.VarChar, 25).Value = _userid;

        // open connection, execute query, close connection
        connection.Open();
        object returnObj = objCMD.ExecuteScalar();

        if(returnObj != null)
        {
           int.TryParse(returnObj.ToString(), out returnValue);
        }

        connection.Close();
    }

    // return newly inserted ID
    return returnValue;
}

Not sure how you can integrate that with your objData class - maybe you need to add a new method to that DAL class for this.

Check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

---

Related Topics