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); pvCon…

Accepted Answer

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.

Using stored procedure output parameters in C#

Solution

Problem