Get ROWCOUNT of SYSTEM PROCEDURE IN SYBASE ASE 12.5

Question

I need to check for a named transaction in another procedure to ensure it has not been executed by anyone else.

But for this question I am trying to leverage a system procedure

EXEC sp_transactions 'xid','MyTran1'  -- This will return a result if it finds a named transaction called MyTran1

I would like know if it returned a row... RowsReturned > 1

So I did the following

BEGIN TRAN MyTran1  -- named transaction
BEGIN TRAN MyTran2  -- named transaction

If I run EXEC sp_transactions 2 rows are returned

If I run EXEC sp_transactions 'xid','MyTran1' - one row is returned

Now I need to do something like

DECLARE @nameTranFound int
SET @nameTranFound = EXEC sp_transactions  'xid','MyTran1'

I tried using @@RowCount, but it always returns 1

EXEC sp_transactions 'xid','Warren1'
SELECT @@RowCount

Answer 1

While you could create a proxy table wrapper for calling sp_transactions, then run select * from <proxy> / select @@rowcount ... that's likely a bit drastic/overkill for what you want.

Instead, I suggest you take a look at the source code for sp_transactions (HINT: it pulls transaction data from master..systransactions) and roll your own code that provides exactly what you want to know.