title: Getting return value from stored procedure in ADO.NET
body: <p>I have a stored procedure, which returns the unique identifier after insertion <code>@@identity</code>. I tried it in the server explorer and it works as expected <code>@RETURN_VALUE = [identifier]</code>.</p>
  <p>In my code I added a parameter called <code>@RETURN_VALUE</code>, with ReturnValue direction first, than any other parameters, but when I run my query with <code>ExecuteNonQuery()</code> that parameter remains empty. I don't know what I've done wrong.</p>
  <p><strong>Stored Procedure</strong></p>
  <pre><code> ALTER PROCEDURE dbo.SetAuction
   (
    @auctionID int,
    @itemID int,
    @auctionType tinyint,
    @reservationPrice int,
    @maxPrice int,
    @auctionEnd datetime,
    @auctionStart datetime,
    @auctionTTL tinyint,
    @itemName nchar(50),
    @itemDescription nvarchar(MAX),
    @categoryID tinyint,
    @categoryName nchar(50)
   ) AS
   IF @auctionID &lt;&gt; 0
    BEGIN
     BEGIN TRAN T1
     
     UPDATE Auction
     SET  AuctionType   = @auctionType,
       ReservationPrice = @reservationPrice,
       MaxPrice    = @maxPrice,
       AuctionEnd    = @auctionEnd,
       AuctionStart   = @auctionStart,
       AuctionTTL    = @auctionTTL
     WHERE AuctionID    = @auctionID;
     
     UPDATE Item
     SET
      ItemName  = @itemName,
      ItemDescription = @itemDescription
     WHERE
      ItemID = (SELECT ItemID FROM Auction WHERE AuctionID = @auctionID);
     
     COMMIT TRAN T1
     
     RETURN @auctionID
    END
   ELSE
    BEGIN
     BEGIN TRAN T1
      INSERT INTO Item(ItemName, ItemDescription, CategoryID)
      VALUES(@itemName, @itemDescription, @categoryID);
      
      INSERT INTO Auction(ItemID, AuctionType, ReservationPrice, MaxPrice, AuctionEnd, AuctionStart, AuctionTTL)
      VALUES(@@IDENTITY,@auctionType,@reservationPrice,@maxPrice,@auctionEnd,@auctionStart,@auctionTTL);
     COMMIT TRAN T1
     RETURN @@IDENTITY
    END
  </code></pre>
  <p><strong>C# Code</strong></p>
  <pre><code>cmd.CommandText = cmdText;
  SqlParameter retval = new SqlParameter(&quot;@RETURN_VALUE&quot;, System.Data.SqlDbType.Int);
  retval.Direction = System.Data.ParameterDirection.ReturnValue;
  cmd.Parameters.Add(retval);
  cmd.Parameters.AddRange(parameters);
  cmd.Connection = connection;
  
  connection.Open();
  cmd.ExecuteNonQuery();
  
  return (int)cmd.Parameters[&quot;@RETURN_VALUE&quot;].Value;
  </code></pre>