Retrieve output parameter of SQL statement

Question

I'm using a table with two columns, customer_id and customer_name.

customer_name is a simple varchar, customer_id is an auto incrementing primary key.

I want to use my C# application to insert a customer_name, and retrieve the value of the customer_id.

Just inserting is simply solved by using

using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
    using (SqlCommand command = new SqlCommand("INSERT INTO custom_customer (customer_name) VALUES (@name);"))
    {
        command.Parameters.Add(new SqlParameter("name", customer));
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }
}

I found some documentation of the OUTPUT statement, which can be used to retrieve a value.

I think the correct syntax would be

INSERT INTO custom_customer (customer_name) 
OUTPUT Inserted.customer_id 
VALUES (@name);

And I figure I need to use

command.ExecuteReader();

But then I'm stuck. How should I go about getting the value from the query?

score 5 · Answer 1 · answered Dec 04 '13 at 17:07

5

You can use ExecuteScalar instead

int id = (int) command.ExecuteScalar();

answered Dec 04 '13 at 17:07

David Pilkington

13,528
3
41
73

score 3 · Accepted Answer · edited Nov 02 '17 at 17:44

First, use the ExecuteNonQuery to write operations. After execute command, you can read the parameter from Parameters collection, since you have set the parameter as a output parameter, for sample:

command.Parameters["name"].Direction = System.Data.ParameterDirection.Output;

command.ExecuteNonQuery();

object name = command.Parameters["name"].Value;

If you want to know what Id the identity column generated after the insert, you could use SCOPE_IDENTITY() sql command, and use the ExecuteScalar() to get the result of command, for sample:

int id;
using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
{
    string sql = @"INSERT INTO custom_customer (customer_name) 
                                        VALUES (@name); 
                   SELECT SCOPE_IDENTITY();"

    using (SqlCommand command = new SqlCommand(sql))
    {
        command.Parameters.Add(new SqlParameter("name", customer));
        connection.Open();
        id = (int) command.ExecuteScalar();
        connection.Close();
    }
}

I think OP wants to return inserted id and not the same value he is passing as paramter — rs., Dec 04 '13 at 17:12
The second answer here was very helpful, just one thing for future readers: replace SELECT SCOPE_IDENTITY() with SELECT CONVERT(int, SCOPE_IDENTITY()). Casting the return value of simply SCOPE_IDENTITY() into an int yields a format exception, since the standard return value is numeric(38,0), which is unhandled. — Nattfrosten, Dec 06 '13 at 09:49

score 2 · Answer 3 · edited Dec 04 '13 at 17:10

2

You need to call ExecuteScalar() to get the ID value:

int ID= (int) Command.ExecuteScalar();

edited Dec 04 '13 at 17:10

Brian

5,069
7
37
47

answered Dec 04 '13 at 17:08

Sudhakar Tillapudi

25,935
5
37
67

score 1 · Answer 4 · answered Dec 04 '13 at 17:18

using (SqlConnection connection = new SqlConnection(AppConstants.ConnectionString))
using (SqlCommand command = new SqlCommand("INSERT INTO custom_customer (customer_name) VALUES (@name);SELECT SCOPE_IDENTITY();"))
{
    command.Parameters.Add(new SqlParameter("name", customer));
    connection.Open();
    int id= (int)Command.ExecuteScalar();
}

Retrieve output parameter of SQL statement

4 Answers4

Linked