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
- 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.
|
