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, Jun 05 '12 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, Jun 05 '12 at 22:40
I have added the opening of the connection and the creating of the command to the code. — Gary, Jun 05 '12 at 22:54
Could you try to commit the transaction before reading the output parameter? — Steve, Jun 05 '12 at 22:59
I have tried to setup a database and tested your code on LinqPAD, the only difference is in the SP where I have removed the prefix [dbo] and the square brakets around [Contract]. Everything works as expected. The last thing to check are the field data type and the Identity flag on your table, are they correct (ID=Int+Identity)? — Steve, Jun 05 '12 at 23:23
This is the definition when I create the table....[Id] [int] IDENTITY(1,1) NOT NULL — Gary, Jun 05 '12 at 23:27
Also, when you talk about a NULL value, do you mean a DBNull.Value that when converted via ToString() is an empty string? or you get an exception? — Steve, Jun 05 '12 at 23:28
A DBNull value that when converted it becomes an empty string. — Gary, Jun 05 '12 at 23:29
When I look at the column in sql server it says the following... Id(PK, int, Not Null) — Gary, Jun 05 '12 at 23:39
Curious that the `Id` column doesn't say `IDENTITY`. As an aside, there have been problems in several versions of SQL Server with `SCOPE_IDENTITY()`. Using an `OUTPUT` clause on the `INSERT` will always get you the correct answer, but it does involve adding another table. See the _Workarounds_ tab [here](http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value#details). — HABO, Jun 06 '12 at 01:09
Just to divide the problem in half, you could add `set @NewId = 42` at the end of your stored procedure and determine whether or not the value makes it back to C#. — HABO, Jun 06 '12 at 01:16
Now that it is 2019, 7 years after the question, readers should check out the OUTPUT clause in modern Sql Server INPUT, UPDATE, and DELETE statements. Even so, getting the value from an output parameter is still valid, even if the output statement by default will just return as a result set, so you wouldn't use an output parameter or return value in the simple case. — Guy Schalnat, Sep 25 '19 at 18:24

score 140 · Answer 1 · edited Mar 10 '21 at 17:41

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.

Thank you! I appreciate your time in answering this for those of us Googling it years later. :-) — John Suit, Sep 16 '14 at 18:30
@marc_s if the size of Varchar output is unknown then how to tackle that problem. — Gunner, Jan 09 '15 at 12:22
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 — user3569147, May 13 '17 at 23:29
@user3569147: **most definitely NOT!!** You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, May 14 '17 at 07:32

score 3 · Answer 2 · edited Jun 07 '12 at 00:07

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 07 '12 at 00:07

Kev

112,868
50
288
373

answered Jun 06 '12 at 05:52

TarasB

2,179
1
22
30

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. :) – Kev Jun 07 '12 at 00:09

Jeremy Thompson · Answer 3 · 2012-06-06T11:40:46.880

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 06 '12 at 11:40

answered Jun 05 '12 at 22:21

Jeremy Thompson

52,213
20
153
256

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. – Tim Schmelter Jun 05 '12 at 22:25
I also receive the following error when I make this change...Object reference not set to an instance of an object. – Gary Jun 05 '12 at 22:30
Thats because the Parameter is called SCOPE_IDENTITY, in SQL; C# is still looking for a parameter called @ContractNumber. – Russ Clarke Jun 05 '12 at 23:04
1

ContractNumber is the value being added to the table. The primary key is called Id. – Gary Jun 05 '12 at 23:11
ExecuteScalar returns an object, not an int. I receive an exception when I execute the code. – Gary Jun 05 '12 at 23:21
Hi Gary, I thought you'd be able to work that bit out, but I edit my answer none-the-less. – Jeremy Thompson Jun 05 '12 at 23:30
I still get back a DBNull value....Object cannot be cast from DBNull to other types. I'm off to my other job,. I'll take this up again tomorrow. Thanks for the help!!! – Gary Jun 05 '12 at 23:59
The column does says it is an identity column – Gary Jun 06 '12 at 22:15
something else must be wrong because this works perfectly for me. – Jeremy Thompson Jun 06 '12 at 23:16
Sorry to delete my answer, the down vote really got to me. I know its bad form on my part and it can get you banned from answering questions. To the down voter its not cool and I would like to know why? – Jeremy Thompson Jul 12 '12 at 12:54

Nitin · Answer 4 · 2016-01-22T11:49:46.130

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 '16 at 11:49

answered Jan 22 '16 at 07:36

Nitin

127
1
5

Using stored procedure output parameters in C#

4 Answers4

Linked

Related