Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 29 January 2012
Getting a stored procedure's result set in a table
 
A question that keeps coming back with some regularity is this one: can I execute a stored procedure (like sp_who, or a custom-written procedure), and get its results as rows in a table? The issue here is that the stored procedure itself just sends its result set to the client -- these results are wanted in a table, but without changing the stored procedure's code.
The answer is: yes, but subject to some restrictions.

Contents:


Before we proceed...
Before proceeding, please note that we'll be looking at a trick here. It works, but if you need this sort of functionality for your production system, you should really modify the stored procedure to put the results in a table directly: the trick described here will not perform as well, and will likely also not be as scalable, as a properly coded stored procedure.

Anyway, to make this work we need to use part of the CIS functionality. It is not widely known that CIS allows you to create a proxy table which you can map to a remotely located stored procedure. When selecting from the proxy table, you execute the stored procedure and get its results returned as rows in the proxy table (another examples of this trick can be found here). Every time you access the proxy table, the procedure is executed, and its results returned, again.

The basic idea
Here is a simple example, whereby we map the proxy table to a stored procedure in the same database. This requires defining a 'loopback' server name first, to points back to ourselves (you likely have this defined already if you ever installed the MDA tables):
sp_addserver loopback, null, @@servername
go
Next, we create the stored procedure and the proxy table:
use my_db
go
create procedure my_proc 
as 
   select name, type, crdate from sysobjects
go
create existing table my_tab 
   (name varchar(30), type char(2), crdate datetime) 
external procedure at 'loopback.my_db..my_proc'
go
When selecting from 'my_tab', we get the result set from the procedure, returned as rows from the proxy table:
1> select * from my_tab
2> go
 name                           type crdate
 ------------------------------ ---- -------------------
 sysobjects                     S    Apr 30 2007  6:54PM
 sysindexes                     S    Apr 30 2007  6:54PM
 syscolumns                     S    Apr 30 2007  6:54PM
 systypes                       S    Apr 30 2007  6:54PM
 syslogs                        S    Apr 30 2007  6:54PM
 syspartitions                  S    Apr 30 2007  6:54PM
 sysgams                        S    Apr 30 2007  6:54PM
 systabstats                    S    Apr 30 2007  6:54PM
 sysprocedures                  S    Apr 30 2007  6:54PM
 syscomments                    S    Apr 30 2007  6:54PM
 syssegments                    S    Apr 30 2007  6:54PM
 sysprotects                    S    Apr 30 2007  6:54PM
 sysusers                       S    Apr 30 2007  6:54PM
 sysalternates                  S    Apr 30 2007  6:54PM
 sysdepends                     S    Apr 30 2007  6:54PM
 syskeys                        S    Apr 30 2007  6:54PM
 syspartitionkeys               S    Apr 30 2007  6:54PM
 sysreferences                  S    Apr 30 2007  6:54PM
[...]
(34 rows affected)


Restrictions: matching columns and datatypes
The first restriction to observe is that you need a column in the proxy table for every column in the procedure's result set; any procedure output for which the proxy table has no column will be discarded. Note that the column names don't matter: the procedure results are mapped to the proxy table in the order in which they appear.
Also, you must ensure the proxy table columns are long enough to contain the actual procedure results. If the column is too short, it will be truncated:
1> create existing table my_tab2 (name2 varchar(5))
2> external procedure at 'loopback.my_db..my_proc'
3> go
1> select * from my_tab2
3> go
 name2
 -----
 sysob
 sysin
 sysco
 systy
 syslo
 syspa
 sysga
 systa
 syspr
 sysco
 sysse
 syspr
[...]
Also, you better make sure the datatypes of the stored procedure results are identical to those of the proxy table columns, or at least can be converted to them:
1> create existing table my_tab3 (name3 int)
2> external procedure at 'loopback.my_db..my_proc'
3> go
1> select * from my_tab3
3> go
Msg 11216, Level 16, State 2:
Server 'X1502', Line 1:
Internal Ct-Lib/Cs-Lib error 33816856: 'cs_convert: cslib user api layer: common
library error: The conversion/operation was stopped due to a syntax error in the
source field.'.
Command has been aborted.
(0 rows affected)
You should also be careful not to include column in the proxy table that have no corresponding column in the procedure's result set. If this happens, nasty internal errors can result:
1> create existing table my_tab4 
2>    (name varchar(30), type char(2), crdate datetime, extra_col varchar(20)) 
3> external procedure at 'loopback.my_db..my_proc'
4> go
Msg 632, Level 20, State 2:
Server 'X1502', Line 1:
Attempt to move memory with an incorrect length of -7856. Maximum allowed length
is 16384.


Restrictions: multiple result sets
Another restriction is that only the first result set from the stored procedure is returned -- any subsequent results sets are quietly discarded:
1> create procedure my_proc2
2> as
3>    select 'abc'
4>    select 'def'
5> go
1> create existing table my_tab5 (name varchar(10))
2> external procedure at 'loopback.my_db..my_proc2'
3> go
1> select * from my_tab5
2> go
 name
 ------------------------------
 abc

(1 row affected)


Other restrictions
Any print statements in the procedure will still send their output to client unconnected to any result sets:
create procedure my_proc3
as 
   print "Hello there!"
   select name, type, crdate from sysobjects 
   print "Hello again!"
go
create existing table my_tab6 
(name varchar(30), type char(2), crdate datetime) 
external procedure at 'loopback.my_db..my_proc3'
go

1> select * into #t from my_tab6
2> go
Hello there!
Hello again!
(42 rows affected)
Another restriction is that you cannot capture the stored procedure's return status in the proxy table.

Lastly, a practical restriction to keep in mind is that a proxy table as discussed here should best be queried directly, probably in a select-into or insert-select statement so as to save the rows in a permanent table.
What should be avoided is making such a proxy table part of a join: if the proxy table happens to become an inner table of the join (meaning it will be repeatedly accessed while the join is processed), then the stored procedure will be executed for every access as part of the join. If the stored procedure does not always return exactly the same data (for example, only the 5 most recently added customer orders), then there is no guarantee that the query results will be what you expect. This problem is basically the same as when querying the historical MDA tables (e.g. monSysStatement, monSysSQLText, etc) -- in fact, the MDA tables are also proxy tables mapped to a procedure, although they are mapped to a native RPC (i.e. embedded in the ASE engine) rather than a user-created stored proc.


Passing parameters to the stored proc
It is actually possible to call the stored procedure with parameter values. For this, the proxy table must contain additional special columns whose names correspond to the stored procedure parameters -- these columns are special because their name must start with an underscore, and because the value specified in the where-clause is passed as a parameter to the stored procedure:
create procedure my_proc4
   @id int
as 
   select name, type, crdate from sysobjects where id <= @id
go
create existing table my_tab7 
(name varchar(30), type char(2), crdate datetime, _id int null) 
external procedure at 'loopback.my_db..my_proc4'
go

1> select * from my_tab7 where _id = 5
2> go

name                           type crdate              _id
------------------------------ ---- ------------------- -----------
sysobjects                     S    Apr 30 2007  6:54PM           5
sysindexes                     S    Apr 30 2007  6:54PM           5
syscolumns                     S    Apr 30 2007  6:54PM           5
systypes                       S    Apr 30 2007  6:54PM           5
sysprocedures                  S    Apr 30 2007  6:54PM           5

(5 rows affected)
Note that the predicate in the where-clause can also use a different operator than '=', but this may affect the results being returned in ways you didn't expect (try 'select * from my_tab7 where _id != 5').

For full details, see the ASE Reference Manual.

Again, please keep in mind that this is a bit of a hack -- the best approach really is to code your stored procs to put its result sets in the table where you want them. But, as hacks go, this one can actually be useful.
 
 This document is located at www.sypron.nl/proctab.html