Using stored procedure output parameters in C#

by

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.

More Related Contents:

  1. Procedure or function xxxxx has too many arguments specified [duplicate]
  2. An unhandled exception of type ‘System.InvalidOperationException’ occurred in System.Data.dll?
  3. How to pass an array into a SQL Server stored procedure
  4. How to execute a stored procedure within C# program
  5. How to pass table value parameters to stored procedure from .net code
  6. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  7. Call a stored procedure with parameter in c#
  8. Calling stored procedure with return value
  9. Getting data from stored procedure with Entity Framework
  10. Passing List to SQL Stored Procedure
  11. Using DateTime in a SqlParameter for Stored Procedure, format error
  12. How to pass XML from C# to a stored procedure in SQL Server 2008?
  13. What is passing parameters to SQL and why do I need it?
  14. Add WHERE clauses to SQL dynamically / programmatically
  15. How to pass a null variable to a SQL Stored Procedure from C#.net code
  16. C# Sql Textbox Select
  17. How to get the connection String from a database
  18. How to directly execute SQL query in C#?
  19. How do I return multiple result sets with SqlCommand?
  20. ExecuteNonQuery for SELECT sql statement returning no rows
  21. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  22. SSIS Task for inconsistent column count import?
  23. Creating a SQL Server table from a C# datatable
  24. Retrieve image from database in asp.net
  25. Entity Framework | Sequence contains more than one matching element
  26. When is it better to store flags as a bitmask rather than using an associative table?
  27. What’s the best way to bulk database inserts from c#?
  28. SQL Server Database Change Listener C#
  29. Should the order of LINQ query clauses affect Entity Framework performance?
  30. How do I connect to a SQL database from C#?

Leave a Comment