Parameter Passing in proxy tables designed to store sp output

A. Pre requisites: Need to have cis properties enabled, loopback server added in sysservers, and setting up an externlogin to loopback from your local
B. Create existing table :
create existing table procinfo ( procname varchar(20), owner varchar(10), tranmode varchar(20) , _procname varchar(767) null )
external procedure at "loopback.sybsystemprocs.dbo.sp_procxmode"
Here the last column in actually the parameter to the procedure sp_procxmode with same name but distinguished by an _ ( underscore )
You can use this in the table select where clause as shown later:

C. Now start selecting ;)

1> select * from procinfo
2>
procname owner tranmode _procname
-------------------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sp_activeroles dbo Any Mode NULL
sp_add_qpgroup dbo Any Mode NULL
sp_add_resource_limi dbo Any Mode NULL
This will take the _procname as NULL as no where clause passed and thus give a listing of sp_procxmode output as stored in procinfo table


1> select * from procinfo where procname='sp_listsuspect_db'
2>
procname owner tranmode _procname
-------------------- ---------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sp_listsuspect_db dbo Any Mode NULL
This will pass the parameter @procname = 'sp_listsuspect_db' as this has been mentioned in where clause and return only for the procedure.
We can use this feature to capture results of stored procedure in tables by providing different parameters…
Cheers,
Ajit