Useful SQL Tricks

6.3.1 How to feed the result set of one stored procedure into another.

6.3.2 Is it possible to do dynamic SQL before ASE 12?

Note: A number of the following tips require CIS to be enabled (at this precise moment, all of them require CIS :-) The optimiser does take on a different slant, however small, when CIS is enabled, so it is up to you to ensure that things don't break when you do turn it on. Buyer beware. Test, test, test and when you have done that, check some more.

6.3.1: How to feed the result set of one stored procedure into another.

I am sure that this is all documented, but it is worth adding here. It uses CIS, as do a number of useful tricks. CIS is disabled by default before 12.0 and not available before 11.5. It is courtesy of BobW from sybase.public.ase.general, full acceditation will be granted if I can find out who he is. Excellent tip!

So, the scenario is that you have a stored procedure, AP_A, and you wish to use the result set that it returns in a query.

Create a proxy table for SP_A.

create table proxy_SP_A (
  a int,
  b int,
  c int,
  _p1 int null,
  _p2 int null
) external procedure
 at "SELF.dbname.dbo.SP_A"

Columns a, b, c correspond to the result set of SP_A. Columns _p1, _p2 correspond to the @p1, @p2 parameters of SP_A. "SELF" is an alias put in sysservers to refer back to the local server.

If you only have one row returned the proxy table can be used with the following:

declare @a int, @b int, @c int
select @a = a, @b = b, @c = c from proxy_SP_B
where _p1 = 3 and _p2 = 5

More rows can be handled with a cursor.

6.3.2: Is it possible to do dynamic SQL before ASE 12?

Again, using CIS, it is possible to fake dynamic SQL. Obviously for this to work, CIS must be enabled. In addition, the local server must be added to sysservers as a remote server. There is a stored procedure, sp_remotesql, that takes as an arguments a remote server and a string, containing SQL.

As before, adding SELF as the 'dummy' server name pointing to the local server as if it were a remote server, we can execute the following:

sp_remotesql "SELF","select * from sysdatabases"

Which will do just what you expect, running the query on the local machine. The stored proc will take 251 (according to its own documentation) arguments of char(255) or varchar(255) arguments, and concatenate them all together. So we can do the following:

1> declare @p1 varchar(255),@p2 varchar(255),@p3 varchar(255), @p4 varchar(255)
2>
3> select @p1 = "select",
4>        @p2 = " name ",
5>        @p3 = "from ",
6>        @p4 = "sysdatabases"
7>
8> exec sp_remotesql "SELF", @p1, @p2, @p3, @p4
9> go
(1 row affected)
 name
 ------------------------------
 bug_track
 dbschema
 master
 model
 sybsystemprocs
 tempdb

(6 rows affected, return status = 0)

Obviously, when the parameters are concatenated, they must form a legal T-SQL statement. If we remove one of the spaces from the above statement, then we see:

1> declare @p1 varchar(255),@p2 varchar(255),@p3 varchar(255), @p4 varchar(255)
2>
3> select @p1 = "select",
4>        @p2 = "name ",
5>        @p3 = "from ",
6>        @p4 = "sysdatabases"
7>
8> exec sp_remotesql "SELF", @p1, @p2, @p3, @p4
9> go
Msg 156, Level 15, State 1
, Line 1
Incorrect syntax near the keyword 'from'.
(1 row affected, return status = 156)

Quick Links – Sybase FAQ