title: Stored procedure MERGE (INSERT OR UPDATE) and return ID issue
body: <p>So I'm trying to learn how to use stored procedures and for the most part I somewhat understand how to use them except for this merge procedure.</p>
  
  <p>What I'm trying to do is use the MERGE procedure to Insert if a row does not already exist and return the ID ELSE IF NOT EXISTS then just return the ID.</p>
  
  <p>Here is currently what I have so far.</p>
  
  <pre><code>CREATE PROCEDURE dbo.authors_InsertOrUpdate
  -- Add the parameters for the stored procedure here
  @FirstName nvarchar(50), 
  @LastName nvarchar(50),
  @id int = NULL OUTPUT
  AS
  MERGE Authors AS target
  USING (SELECT @FirstName, @LastName) AS source (FirstName, LastName)
  ON target.FirstName = source.FirstName AND target.LastName = source.LastName
  WHEN MATCHED THEN
      UPDATE 
      SET @id = SCOPE_IDENTITY()
  WHEN NOT MATCHED THEN INSERT 
      (FirstName, LastName) VALUES (source.FirstName, source.LastName);
      SET @id = SCOPE_IDENTITY()
  </code></pre>
  
  <p>Everything works fine as far as inputting the values/information into the database and finding an existing one but I'm not getting my ID value back correctly. Here is my method that uses it..</p>
  
  <pre><code>private static int storeAuthors(string firstName, string lastName)
          {
              String commandText = "dbo.authors_InsertOrUpdate";
              using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AmazonCrawler.Properties.Settings.database"].ToString()))
              {
                  using (SqlCommand command = new SqlCommand(commandText, connection))
                  {
                      command.CommandType = CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@FirstName", firstName));
                      command.Parameters.Add(new SqlParameter("@LastName", lastName));
                      SqlParameter authorId = new SqlParameter("@id", SqlDbType.Int);
                      authorId.Direction = ParameterDirection.Output;
                      command.Parameters.Add(authorId);
  
                      connection.Open();
                      command.ExecuteNonQuery();
                      connection.Close();
  
                      return Convert.ToInt32(authorId.Value);
                  }
              }
          }
  </code></pre>
  
  <p>Please help me fix this!</p>
  
  <p>Thanks in advance.</p>
  
  <p>---------EDIT------------</p>
  
  <p>Updated the procedure to this yet I'm still receiving a null value in my code when i try to return the value</p>
  
  <pre><code>CREATE PROCEDURE dbo.authors_InsertOrUpdate
      -- Add the parameters for the stored procedure here
      @FirstName nvarchar(50), 
      @LastName nvarchar(50),
      @id int OUTPUT
  AS
  MERGE Authors AS target
  USING (SELECT @id, @FirstName, @LastName) AS source (id, FirstName, LastName)
  ON target.FirstName = source.FirstName AND target.LastName = source.LastName
  WHEN NOT MATCHED THEN 
      INSERT (FirstName, LastName) VALUES (source.FirstName, source.LastName);
      SET @id = SCOPE_IDENTITY()
  </code></pre>
  
  <p>Edit reference 1
  <img src="https://i.stack.imgur.com/oCfyi.jpg" alt="enter image description here"></p>
  
  <p>Edit reference 2</p>
  
  <p><img src="https://i.stack.imgur.com/JwAJv.jpg" alt="enter image description here"></p>
  
  <p>Edit reference 3</p>
  
  <pre><code>ALTER PROCEDURE dbo.authors_InsertOrUpdate
      -- Add the parameters for the stored procedure here
      @FirstName nvarchar(50), 
      @LastName nvarchar(50),
      @id int OUTPUT
  
  AS
  MERGE Authors AS target
  USING (SELECT @id, @FirstName, @LastName) AS source (ID, FirstName, LastName)
  ON target.FirstName = source.FirstName AND target.LastName = source.LastName
  WHEN MATCHED THEN 
      UPDATE SET @id = target.ID
  WHEN NOT MATCHED THEN 
      INSERT (FirstName, LastName) VALUES (source.FirstName, source.LastName);
      SET @id = SCOPE_IDENTITY()
  
  SELECT @id
  </code></pre>
  
  <p>Still receiving same error listed in reference 2. 
  =( no solution yet.</p>
  
  <p>here is my passed in values with a return of zero as an id
  <img src="https://i.stack.imgur.com/ngCnu.jpg" alt="enter image description here"></p>
  
  <p>and here is my database</p>
  
  <p><img src="https://i.stack.imgur.com/GRFrq.jpg" alt="enter image description here"></p>
  
  <p>Edit reference 4</p>
  
  <p>Tried changing my parameters when putting in the values to this as I thought perhaps closing it before getting the value was causing the problem but no luck</p>
  
  <pre><code>private static int storeAuthors(string firstName, string lastName)
          {
              String commandText = "dbo.authors_InsertOrUpdate";
              using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AmazonCrawler.Properties.Settings.database"].ToString()))
              {
                  using (SqlCommand command = new SqlCommand(commandText, connection))
                  {
                      command.CommandType = CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@FirstName", firstName));
                      command.Parameters.Add(new SqlParameter("@LastName", lastName));
                      SqlParameter authorId = new SqlParameter("@id", SqlDbType.Int);
                      authorId.Direction = ParameterDirection.Output;
                      command.Parameters.Add(authorId);
  
                      connection.Open();
                      command.ExecuteNonQuery();
                      int value = Convert.ToInt32(authorId.Value);
                      connection.Close();
  
                      return value;
                  }
              }
          }
  </code></pre>