need a return value from the stored procedure.

Question

I am trying to update a SQL-SERVER table using a Stored-Procedure which is called in a web-Service.
here i need a return value from the stored procedure so that i can send true or false to the user regarding the update confirmation.
I am Using C# as the language and ADO.Net architecture for connecting and manipulating SQL-Server Tables.
I am also using SQLDataConnection(System.Data.SqlClient).
Thanks in Advance

Pankaj Rana · Answer

You can use Output parameter to get return value
visit this Output parameter

Mukesh Kumar · Answer

hi

Upamanyu Roy Choudhury · Answer

Hi zakir,
I am giving a  sample which shows a Sp returning identity value and in the GUI we are accepting the return value.
The SP goes like this
CREATE PROCEDURE dbo . Ins_Item_Master ( @Serial VARCHAR ( 50 ),
@ItemId INT ,
@ItemType VARCHAR ( 100 ))
AS
BEGIN
DECLARE @ReturnValue INT
SET  NOCOUNT ON
INSERT INTO item_master ( Serial , ItemId , ItemType )
VALUES ( @Serial , @ItemId , @ItemType )
SELECT SCOPE_IDENTITY ()
END
UI CALL
No we are calling it from the C# code behind and we print it in the Web.
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[16].ToString());
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("dbo.Ins_Item_Master", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Serial", "ser_" + chkItems.SelectedItem.Value);
cmd.Parameters.AddWithValue("@ItemId", 1);
cmd.Parameters.AddWithValue("@ItemType", chkItems.SelectedItem.Text);
object returnValue = cmd.ExecuteScalar();
int intCount = (returnValue == null ? -1 : Convert.ToInt32(returnValue.ToString()));
if (intCount >0)
{
Response.Write("Insert Successful");
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
I think it should help your cause.
Please keep me posted and mark it as an answer if you find my suggestion as helpful.