Using stored procedure output parameters in C#

Question

I am having a problem returning an output parameter from a Sql Server stored procedure into a C# variable. I have read the other posts concerning this, not only here but on other sites, and I cannot get it to work. Here is what I currently have. Currently I am just trying to print the value that comes back. The following code returns a null value. What I an trying to return is the primary key. I have tried using @@IDENTITY and SCOPE_INDENTITY() (i.e. SET @NewId = SCOPE_IDENTITY()).

Stored Procedure:

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),

    @NewId int OUTPUT
AS
BEGIN

    INSERT into [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber)

    Select @NewId = Id From [dbo].[Contracts] where ContractNumber = @ContractNumber
END

Opening the database:

pvConnectionString = "Server = Desktop-PC\\SQLEXPRESS; Database = PVDatabase; User ID = sa;
    PASSWORD = *******; Trusted_Connection = True;";

try
{
    pvConnection = new SqlConnection(pvConnectionString);
    pvConnection.Open();
}
catch (Exception e)
{
    databaseError = true;
}

Executing the command:

pvCommand = new SqlCommand("usp_InsertContract", pvConnection);

pvCommand.Transaction = pvTransaction;
pvCommand.CommandType = CommandType.StoredProcedure;    

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));

SqlParameter pvNewId = new SqlParameter();
pvNewId.ParameterName = "@NewId";
pvNewId.DbType = DbType.Int32;
pvNewId.Direction = ParameterDirection.Output;
pvCommand.Parameters.Add(pvNewId);

try
{
    sqlRows = pvCommand.ExecuteNonQuery();

    if (sqlRows > 0)
        Debug.Print("New Id Inserted =  ", 
            pvCommand.Parameters["@NewId"].Value.ToString()); 
    }
    catch (Exception e)
    {
        Debug.Print("Insert Exception Type: {0}", e.GetType());
        Debug.Print("  Message: {0}", e.Message);
    }
}

I'd also use SCOPE_IDENTITY() instead of requerying the same table., and in case you get a 'query can return multiple results' error, when assigning to a single output var. — JMC
– JMC, Commented Jun 5, 2012 at 22:35
Do I use the set command to do this? SET NewId = SCOPE_IDENTITY()? I replaced the select statement with the following...SET @NewId = SCOPE_IDENTITY() and I still receive a null value back. — Gary
– Gary, Commented Jun 5, 2012 at 22:40
I have added the opening of the connection and the creating of the command to the code. — Gary
– Gary, Commented Jun 5, 2012 at 22:54
Could you try to commit the transaction before reading the output parameter? — Steve
– Steve, Commented Jun 5, 2012 at 22:59

Ali Bdeir · Accepted Answer · 2021-03-10 17:41:38Z

151

I slightly modified your stored procedure (to use SCOPE_IDENTITY) and it looks like this:

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),
    @NewId int OUTPUT
AS
BEGIN
    INSERT INTO [dbo].[Contracts] (ContractNumber)
    VALUES (@ContractNumber)

    SELECT @NewId = SCOPE_IDENTITY()
END

I tried this and it works just fine (with that modified stored procedure):

// define connection and command, in using blocks to ensure disposal
using(SqlConnection conn = new SqlConnection(pvConnectionString ))
using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
        
    // set up the parameters
    cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7);
    cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;

    // set parameter values
    cmd.Parameters["@ContractNumber"].Value = contractNumber;

    // open connection and execute stored procedure
    conn.Open();
    cmd.ExecuteNonQuery();

    // read output value from @NewId
    int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
    conn.Close();
}

Does this work in your environment, too? I can't say why your original code won't work - but when I do this here, VS2010 and SQL Server 2008 R2, it just works flawlessly....

If you don't get back a value - then I suspect your table Contracts might not really have a column with the IDENTITY property on it.

edited Mar 10, 2021 at 17:41

Ali Bdeir

4,42613 gold badges63 silver badges129 bronze badges

answered Jun 6, 2012 at 5:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user416752 Over a year ago

Thank you! I appreciate your time in answering this for those of us Googling it years later. :-)

Gunner Over a year ago

@marc_s if the size of Varchar output is unknown then how to tackle that problem.

user3569147 Over a year ago

cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output; needs to be altered to cmd.Parameters.AddWithValue("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output; This is because it has depreciated

Kev · Accepted Answer · 2012-06-07 00:07:30Z

3

Before changing stored procedure please check what is the output of your current one. In SQL Server Management run following:

DECLARE @NewId int
EXEC    @return_value = [dbo].[usp_InsertContract]
            N'Gary',
            @NewId OUTPUT
SELECT  @NewId

See what it returns. This may give you some hints of why your out param is not filled.

edited Jun 7, 2012 at 0:07

Kev

120k53 gold badges308 silver badges396 bronze badges

answered Jun 6, 2012 at 5:52

TarasB

2,4381 gold badge25 silver badges32 bronze badges

1 Comment

Kev Over a year ago

This just about works as an answer, but probably should be a comment, however I can't convert it because of the multiple @ character usage (even mods are denied that right) and I can't be bothered editing it to make it work. :)

mostafa toloo · Accepted Answer · 2021-07-02 08:45:09Z

2

I had a similar problem and first closed the connection and then read the parameters and it worked fine. you can use pvConnection.Close(); before read the output parameter try { pvConnection = new SqlConnection(pvConnectionString); pvConnection.Open(); } catch (Exception e) { databaseError = true; }

pvCommand = new SqlCommand("usp_InsertContract", pvConnection);

pvCommand.Transaction = pvTransaction;
pvCommand.CommandType = CommandType.StoredProcedure;    

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));

SqlParameter pvNewId = new SqlParameter();
pvNewId.ParameterName = "@NewId";
pvNewId.DbType = DbType.Int32;
pvNewId.Direction = ParameterDirection.Output;
pvCommand.Parameters.Add(pvNewId);

try
{
    sqlRows = pvCommand.ExecuteNonQuery();
    pvConnection.Close();
    if (sqlRows > 0)
        Debug.Print("New Id Inserted =  ", 
            pvCommand.Parameters["@NewId"].Value.ToString()); 
    }
    catch (Exception e)
    {
        Debug.Print("Insert Exception Type: {0}", e.GetType());
        Debug.Print("  Message: {0}", e.Message);
    }
}

edited Jul 2, 2021 at 8:45

answered Jun 29, 2021 at 12:09

mostafa toloo

613 bronze badges

4 Comments

Marcus Vinicius Pompeu Over a year ago

Mostafa, could you share with us this code?

mostafa toloo Over a year ago

excuse me, I did not understand you!

Marcus Vinicius Pompeu Over a year ago

No problem :-) I see you edited the answer in July with the code. Thanks.

mostafa toloo Over a year ago

Oh, excuse me. Now I see this.

Jeremy Thompson · Accepted Answer · 2012-06-06 11:40:46Z

1

Stored Procedure.........

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7)
AS
BEGIN

    INSERT into [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber)

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
END

C#

pvCommand.CommandType = CommandType.StoredProcedure;

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));
object uniqueId;
int id;
    try
    {
    uniqueId = pvCommand.ExecuteScalar();
     id = Convert.ToInt32(uniqueId);
    }
    catch (Exception e)
    {
        Debug.Print("  Message: {0}", e.Message);
    }
}

EDIT: "I still get back a DBNull value....Object cannot be cast from DBNull to other types. I'll take this up again tomorrow. I'm off to my other job,"

I believe the Id column in your SQL Table isn't a identity column.

enter image description here

edited Jun 6, 2012 at 11:40

answered Jun 5, 2012 at 22:21

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

10 Comments

Tim Schmelter Over a year ago

But this answers not the question why the output parameter for the new id returns null. With ExecuteScalar you can retrieve a return value not an output parameter.

Gary Over a year ago

I also receive the following error when I make this change...Object reference not set to an instance of an object.

Russ Clarke Over a year ago

Thats because the Parameter is called SCOPE_IDENTITY, in SQL; C# is still looking for a parameter called @ContractNumber.

Gary Over a year ago

ContractNumber is the value being added to the table. The primary key is called Id.

Gary Over a year ago

ExecuteScalar returns an object, not an int. I receive an exception when I execute the code.

|

Nitin · Accepted Answer · 2016-01-22 11:49:46Z

0

In your C# code, you are using transaction for the command. Just commit the transaction and after that access your parameter value, you will get the value. Worked for me. :)

edited Jan 22, 2016 at 11:49

answered Jan 22, 2016 at 7:36

Nitin

1971 silver badge6 bronze badges

Collectives™ on Stack Overflow

Using stored procedure output parameters in C#

5 Answers 5

3 Comments

1 Comment

4 Comments

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

4 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related