T-SQL: returning the new INSERT identity to C#

Question

i'm putting values into SQL Server using a Stored Procedure. The Procedure will add an ID to the row that is added. I need to get this ID back to my code.

Currently I can get the I see the output id in the OUTPUT window of Visual Studio, but can't seem to capture it in my code. Here is a summarized version of the proc:

SQL:

CREATE PROCEDURE dbo.DoSomething
(
    @var1 INT = NULL,
    @var2 INT = NULL,
    @var3 DATE = NULL
)
AS

BEGIN

    INSERT INTO atable
    (
        vara,
        varb,
        varc
    )
    VALUES
    (
        @var1,
        @var2,
        @var3
    )

    RETURN SCOPE_IDENTITY()

END

C#:

int result = 0;

/// create command
SqlCommand cmd = new SqlCommand("DoSomething", this.OpenSqlConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@var1", thing.value1);
cmd.Parameters.AddWithValue("@var2", thing.value2);
cmd.Parameters.AddWithValue("@var3", thing.value3);


/// send data to db
result = (int)cmd.ExecuteScalar();

So I'm getting an error: Object reference not set to an instance of an object. when it gets to the (int)cmd.ExecuteScalar().

Any ideas?

Not directly related to the error you're getting, but don't use RETURN with SCOPE_IDENTITY(). Stored Procedures only allow for RETURN to work with data type int. Since your identity can take on other data types, you could potentially return something that is not of data type int. — Justin Skiles, Mar 06 '11 at 18:20

score 12 · Accepted Answer · edited Jan 27 '15 at 00:43

12

...
SELECT SCOPE_IDENTITY()

The RETURN value actually comes back as an special OUTPUT parameter. SELECT gives a result set for ExecuteScalar.

You can use the OUTPUT clause instead of a separate SELECT too:

...
AS
BEGIN
    INSERT INTO atable
    (
        vara,
        varb,
        varc
    )
    OUTPUT INSERTED.IDCol
    VALUES
    (
        @var1,
        @var2,
        @var3
    )
END
GO

This will work for multiple rows too.

edited Jan 27 '15 at 00:43

Air

8,274
2
53
88

answered Mar 06 '11 at 17:49

gbn

422,506
82
585
676

4

Just to clarify your statement for anyone else, the "OUPUT INSERTED.IDCol" line, the IDCol refers to the ID Column that you have specified as Identity. So if your column name is "TableId" than it would be OUTPUT INSERTED.TableId – Tomasz Iniewicz Mar 06 '11 at 19:05

Oded · Answer 2 · 2011-03-06T19:05:12.660

5

You need to add another parameter that has a direction of ReturnValue:

var param = cmd.Parameters.Create("@ReturnValue");
param.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);

The value of this parameter after execution will be the return value:

var newId = cmd.Parameters["@ReturnValue"].Value;

Or

var newId = param.Value;

edited Mar 06 '11 at 19:05

answered Mar 06 '11 at 17:52

Oded

489,969
99
883
1,009

Hi, i was having trouble getting this to return correctly. Thanks for the suggestion though. After trying multiple ways, I ended up trying @gbn's suggestion and that seemed to work. – Tomasz Iniewicz Mar 06 '11 at 19:03
@Tomaszewski - Please note my original code was not _adding_ the parameter to the parameters collection. Answer updated. – Oded Mar 06 '11 at 19:05
Right, it was a seperate param variable. I than added the @ReturnValue param inside the Stored Procedure. I kept on getting a value back, and it was always the number 8. I couldn't figure out why. – Tomasz Iniewicz Mar 06 '11 at 19:09
@Tomaszewski - You don't need to add the ReturnValue parameter to the stored procedure parameter list, you use the `RETURN` statement for that. – Oded Mar 06 '11 at 19:11

score 0 · Answer 3 · answered Mar 06 '11 at 17:54

0

SqlParameter retval = cmd.Parameters.Add("@returnValue", SqlDbType.Int); 
            retval.Direction = ParameterDirection.ReturnValue;

answered Mar 06 '11 at 17:54

Michaël

6,676
3
36
55

T-SQL: returning the new INSERT identity to C#

3 Answers3

Linked