How to get stored procedure resultset into a table? - Method 2 - Component Integration Service

In my last article I tolld you how we can get the resultset from a stored procedure back into the calling stored procedure or a batch, however the previous method wanted us to do lots of modifications to the existing stored procedure. What if, we are asked to get the resultset of the stored procedure without doing any modifications to the existing called stored procedure? For example - what if someone asks us to use the output from sp_spaceused system stored procedure to get the total number of rows in the table under question?

Well in that case CIS or Component Integration Service in Sybase ASE comes to the rescue. Component Integration Service via the use of proxy tables will help us achieve our desired result. How? Well I will list all the steps required while using CIS and finally we will be able to get what we want. The steps which we must follow to use the CIS are as follows -

Feeding Stored Procedure Result in Sybase Table

1. Add a loopback server if it already does not exists in the master..sysservers tables in the ASE. This can be done via the following query – sp_addserver ‘loopback’, ‘ASEnterprise’, <SERVER NAME>. (loopback is just an alias name suggesting the same server to which we are connected).

2. As the login and password for the server will be the same hence no need to add external login.

3. If we need to fetch the number of rows of a very large table TAB in our database MyDB then we can use the system procedure sp_spaceused for the same, however to get the specific output for our table we need to create a wrapper stored procedure for the same proc1. (This procedure can be made generic by passing parameter to the stored procedure.)

Create procedure proc1 as

begin

   sp_spaceused TAB

end

go

4. Now create the existing table (proxy table) mapped to the stored procedure proc1 to get the required result as follows –

create existing table Temp_Table (

name varchar(30) null,

rowtotal int null,

reserved varchar(30) null,

data varchar(30) null,

index_size varchar(30) null,

unused varchar(30) null

)

external procedure at ‘loopback.MyDb..proc1’

go

5. This will create the structure for the Temp_Table. Now we can perform the select operation on the Temp_Table table just like any other normal Sybase table. However behind the scene Temp_Table is actually calling the stored procedure proc1 to fetch the required result.

Note – For more details refer to the Sybase manuals on CIS (Component Integration Services). Also, for this feature to work CIS must be enabled on the server which can be checked by the following –

sp_configure ‘enable cis’ and the version of the cis installed can be checked through isql via the global variable @@cis_version.