Application Level Transaction Vs Stored Proc level transaction

Hi, I'm choosing between transaction on stored proc level and transaction on application level. You see:

Code:

---

DECLARE        @ValidationCode INTEGER
BEGIN TRAN
SET @ValidationCode = 0
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))

... do other stuff...

COMMIT TRAN

CREATE PROCEDURE sp_CreateSupplier(
        @supplier_no INTEGER,
        @type VARCHAR(8),
        @group_no INTEGER = NULL,
        @short_name VARCHAR(50) = NULL,
        @full_name VARCHAR(100) = NULL,
        @address VARCHAR(100) = NULL,
        @country_code VARCHAR(10) = NULL,
        @status VARCHAR(8) = NULL,
        @createBy INTEGER,
        @ownerGroup INTEGER,
        @ValidationCode INTEGER OUTPUT
)
AS
BEGIN

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

        -- STEP 1: Validation
        SET @ValidationCode = 0
        IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)
                BEGIN
                SET @ValidationCode = -10
                ROLLBACK TRANSACTION
                RETURN
                END
        ...
COMMIT TRAN
END

---

The above snippet will result in:

Code:

---

Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

---

Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):

Code:

---

Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier

  Dim sql As String = "sp_CreateSupplier"
  Dim cmd As IDbCommand
  Dim rdr As IDataReader
  Dim param As IDataParameter
  Dim factory As IDbProviderFactory
  Dim validationCode As Int32 = 0

  Try
    If _conn.State <> ConnectionState.Open Then
      _conn.Open()
    End If

    factory = apputil.GlobalVariables.ProviderFactory
    cmd = factory.CreateCommand(sql, Conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Transaction = tran

    ...
    rdr = cmd.ExecuteReader()
    ...
  Catch
    ...
  Finally
      ...
  End Try
End Function

---

What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:

Code:

---

vconn = vFactory.CreateConnection(...)
tran = vconn.BeginTransaction(...serializable...)
SupplierBean.Create(..., tran)
AnotherBean.Update(..., tran)
tran.Commit()

---

So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?

Thanks in advance!