title: C# SQL stored procedure (which inserts) - pass parameters and retrieve parameter
body: <p>I have a stored procedure on my server that inserts some parameters and returns the <code>ID</code> that was inserted. I am writing a form to do this easily but I cannot seem to get the parameter which is passed back. </p>
  
  <p>To save you doing a whole bunch of possibly pointless reading, it's probably better to just pay attention to my C# code and let me know what I need to do in order to pass parameters and get one in return.</p>
  
  <p><strong>C# Default.aspx</strong></p>
  
  <pre><code>connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionInfo"]);
  sql = "aStoredProc";
  
  command = new SqlCommand(sql, connection);
  command.CommandType = CommandType.StoredProcedure;
  
  command.Parameter.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = sFirstname;
  command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname;
  
  connection.Open(); 
  int ID = command.ExecuteNonQuery();
  connection.Close();
  </code></pre>
  
  <p><strong>SQL aStoredProc</strong></p>
  
  <pre><code>IF EXISTS(SELECT * FROM aTable WHERE ID = @ID)
    -- User exists, update details
    BEGIN
      BEGIN TRAN
        UPDATE aTable
          SET 
            FirstName = @FirstName,
            SurName = @SurName,
            LastUpdate = GetDate()
          WHERE ID = @ID
  
          IF (@@Error != 0)
            ROLLBACK TRAN
          ELSE
            COMMIT TRAN
          END
  ELSE
    -- New user
    BEGIN
      BEGIN TRAN
        INSERT aTable (
          FirstName,
          SurName,
          GetDate()
        )
          VALUES (
            @FirstName, 
            @SurName,
            @LastUpdate
        ) 
  
        SELECT @ID = @@IDENTITY
  
        IF (@@Error != 0)
          ROLLBACK TRAN
        ELSE
          COMMIT TRAN
        END
  </code></pre>
  
  <p>The parameter <code>@ID</code> is listed in the stored proc as:</p>
  
  <pre><code>@ID (int, Input/Output, No default)
  </code></pre>
  
  <p>and proc has 'Return integer'. This used to work fine with a VBA solution prior to a SQL Server 2005 upgrade. </p>
  
  <p>Thanks in advance.</p>