Return Value in Stored Procedure:

In Stored Procedure :

Create procedure usp_sample_procedure
@record_id int = null

as
SET NOCOUNT ON
begin

 declare @totalError int

begin Tran t1

insert into tbl_sample();

 if(@@error<>0)     
  begin     
   set @totalError=@@error     
  end

 if(@totalError<>0)     
  begin     
   rollback tran t1     
  end     
  else     
  begin      
   commit tran t1     
  end
return @record_id  
end
 In c#.net:

 SqlParameter sqlPar = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
 sqlPar.Direction = ParameterDirection.ReturnValue;
 sqlCommand.ExecuteNonQuery();
 recordid = int.Parse(Convert.ToString(sqlCommand.Parameters["@ReturnValue"].Value));