Simulating dynamic SQL through CIS This is a demo of how you can use certain CIS (Component Integration Services) features in ASE 11.5 or 11.9 to simulate dynamic SQL.

ASE 12.0 contains a new feature called execute immediate which lets you execute a text string containing dynamically created SQL queries; however, this isn't possible in ASE 11.5.x or 11.9.x. This demo shows how to achieve basically the same functionality in ASE 11.5.x / 11.9.x.

Below on this page, you can download SQL scripts which will create a stored procedure named sp_exec_dynsql. This procedure takes a text string as an argument and executes the SQL statements contained in the text string. Here's an example: the below procedure myproc takes two arguments, being a column name and a table name. It will then select the specified column from the specified table using sp_exec_dynsql:

create procedure myproc @col_name varchar(32), @tab_name varchar(70) as begin declare @cmd varchar(255) select @cmd = "select " + @col_name + "from " + @tab_name exec sp_exec_dynsql @cmd end You can then do things like: 1> exec myproc "pub_name", "pubs2..publishers" 2> go pub_name ---------------------------------------- New Age Books Binnet & Hardley Algodata Infosystems (3 rows affected) Behind the scenes, this works as follows: using a CIS-related trick, you can access the local server as if it were a remote server. You can then use sp_remotesql to execute any command string in that not-so-remote server. Because sp_remotesql takes a text string as an argument, you can create any SQL statement dynamically and then execute it.

Note that you must be running ASE 11.5 or later (earlier versions do not have the CIS functionality included), and you must run set up the server for these tricks to work.

Note that there are also some other tricks to simulate dynamic SQL which will work in any ASE version, including versions 4.x, 10 and 11.0: go here to find out how to use it.

Installation instructions

• Download everything you need here.

• Now you should run two setup scripts to prepare your server for using CIS: run CISXP_SETUP1.SQL, restart your server and then run CISXP_SETUP2.SQL. Note that you should ensure your @@servername matches your servername in the interfaces file.

• Next, you can run the script SP_EXEC_DYNSQL.SQL, and then finally you're ready to execute the procedure sp_exec_dynsql.

• Lastly, you should be aware of some limitations and potential problems related to these tricks: follow this link for more info.