CODEWITHSUNDEEP
c#ado.net
WebMonster
WebMonster

Reputation: 3031

Getting return value from stored procedure in ADO.NET

I have a stored procedure, which returns the unique identifier after insertion @@identity. I tried it in the server explorer and it works as expected @RETURN_VALUE = [identifier].

In my code I added a parameter called @RETURN_VALUE, with ReturnValue direction first, than any other parameters, but when I run my query with ExecuteNonQuery() that parameter remains empty. I don't know what I've done wrong.

Stored Procedure

 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 <> 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

C# Code

cmd.CommandText = cmdText;
SqlParameter retval = new SqlParameter("@RETURN_VALUE", 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["@RETURN_VALUE"].Value;

Upvotes: 28

Views: 69479

Answers (5)

Florian Reischl
Florian Reischl

Reputation: 3856

Just tried on my box and this works for me:

In SQL Server:

DROP PROCEDURE TestProc;
GO
CREATE PROCEDURE TestProc
AS
    RETURN 123;
GO

In C#

string cnStr = "Server=.;Database=Sandbox;Integrated Security=sspi;";
using (SqlConnection cn = new SqlConnection(cnStr))
{
    cn.Open();
    using (SqlCommand cmd = new SqlCommand("TestProc", cn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        
        SqlParameter returnValue = new SqlParameter();
        returnValue.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(returnValue);

        cmd.ExecuteNonQuery();
        Assert.AreEqual(123, (int)returnValue.Value);
    }
}

Upvotes: 40

erhanar
erhanar

Reputation: 1

you can use standart ways that you use before in normal queries but in Sql command you must write EXEC before your store procedure name and dont use commandtype like this :

 SqlConnection con = new SqlConnection(["ConnectionString"])
    SqlCommand com = new SqlCommand("EXEC _Proc @id",con);
    com.Parameters.AddWithValue("@id",["IDVALUE"]);
    con.Open();
    SqlDataReader rdr = com.ExecuteReader();
    ArrayList liste = new ArrayList();
    While(rdr.Read())
    {
    liste.Add(rdr[0]); //if it returns multiple you can add them another arrays=> liste1.Add(rdr[1]) ..
    }
    con.Close();

Upvotes: 0

Prashant Gupta
Prashant Gupta

Reputation: 641

Some one can also use this simple and short method to calculate return value from SP

In SQL:

Create Table TestTable 
(
 Int Id
)

CREATE PROCEDURE Proc_TestProc
 @Id
 AS
   Begin
     Set NOCOUNT ON  //Use this line if you don't want to return any message from SQL

     Delete from TestTable where Id = @Id
     return 1

     Set NOCOUNT OFF //NOCOUNT OFF is Optional for NOCOUNT ON property
   End

Sql Server always returns Int type value only.

and in C#

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString()))
using (SqlCommand cmd = new SqlCommand("Proc_TestProc", conn))
{
 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.AddWithValue("@Id", 1);
 var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
 returnParameter.Direction = ParameterDirection.ReturnValue;

 conn.Open();
 cmd.ExecuteNonQuery();
 var result = returnParameter.Value;
}

You can also check your return value in SQL by using this command:

DECLARE @return_status int;
EXEC @return_status = dbo.[Proc_TestProc] 1;
SELECT 'Return Status' = @return_status;
print 'Returned value from Procedure: ' + Convert(varchar, @return_status); // Either previous or this line both will show you the value of returned value

Upvotes: 1

WebMonster
WebMonster

Reputation: 3031

I solved the problem: you have to set SqlCommand.CommandType to CommandType.StoredProcedure in order to get return values and/or output parameters. I haven't found any documentation about that, but now everything works.

Upvotes: 6

Marc Gravell
Marc Gravell

Reputation: 1062855

Do you get the value of you EXEC in TSQL? I wonder if refactoring the TSQL would help (and using SCOPE_IDENTITY():

so change:

COMMIT TRAN T1
RETURN @@IDENTITY

to:

SET @auctionID = SCOPE_IDENTITY()
COMMIT TRAN T1
RETURN @auctionID

(I would also change the other @@IDENTITY to SCOPE_IDENTITY())

As a minor optimisation, you could also use:

return (int)retval.Value;

but this side of things should have worked "as is" from what I can see (hence why I'm focusing on the TSQL).

Upvotes: 4

Related Questions