Simulating dynamic SQL in ASE

Simulating dynamic SQL in ASE


With a certain regularity, Sybase users ask questions like "How can I use dynamic SQL in ASE ?" in the Sybase-related newsgroups. There are a number of different answers to this question.

The elegant way: execute-immediate (ASE 12.0+)

First, if you're running ASE 12.0 or later, you can use the execute immediate feature, which gives you full dynamic SQL capabilites, as it lets you execute dynamically generated SQL command strings. A simple example:
declare @c varchar(25)
select @c = "select getdate()"
exec (@c)

While this example is hardly something to get excited about, execute-immediate has opened a new dimension to the sort of thing you can do with T-SQL. The most important characteristic is that you can now create stored procedures (and triggers) that are independent of the schema of tables -- a highly useful thing to do.

The clumsy way: simulate dynamic SQL(ASE pre-12.0)

First, If you don't have ASE 12.0, (so you're on version 11.9.x or before), then your ASE version won't support dynamic SQL. However, you can still simulate certain types of dynamic SQL statements through a variety of tricks. These will work in ASE 11.9.x and earlier versions, and even in old stuff such as version 4.9.x.

These are different ways of doing this:

If you're using ASE version 11.5.x or 11.9.x, you can simulate dynamic SQL by using the CIS-related stored procedure sp_remotesql. You'll need to use a backdoor trick which has some drawbacks, but otherwise works very well. It also provides almost as much functionality as the execute immediate in ASE 12.0, but it's quite a bit more complicated to get it all set up. Go here to get details on how to set this up and download some examples.

For any version of ASE, including 4.9.x, 10, 11.0, 11.5 and 11.9, you can achieve quite a few things by creatively employing existing commands and procedures. However, note the following: while offering some possibly useful ideas, there are significant limitations on what's possible with this approach, so this is mainly aimed at inspiration rather than at solving a wide range of real-life problems.


--------------------------------------------------------------------------------

Creating a table with a variable name

While the pre-12.0 Sybase Transact-SQL syntax does not allow you to use a variable for the table name in the create table statement, it is possible to achieve the same result with the following sequence:

create table some_unused_name
( column_1 int, column_2 varchar(80) )

exec sp_rename "some_unused_name",
"the_final_table_name"

By putting these statements in a stored procedure, you can effectively create a table with a variable name.
Note that you can also rename the columns of the newly created table:

exec sp_rename "final_table_name.column_1",
"Column_Number_One"


CHECK MORE OF THIS ON http://www.sypron.nl/dynsql.html


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.

CHECK MORE OF THIS ON http://www.sypron.nl/dynsqlcis.html