(back)
 
Sybase ASE
All versions
Last updated: 31 August 2003
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 capabilities, 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.
My book "Tips, Tricks & Recipes for Sybase ASE" has a chapter dedicated to execute-immediate, describing not only the inner workings, but also a wide range of possible applications. This is probably the most complete writeup about execute-immediate that's available.

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.


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"

Creating a table with a variable number of columns
Continuing the above line of reasoning, you can create a stored procedure which takes a number of parameters specifying the number and/or datatype of the columns the table should have. The procedure first creates the table as above, and then issues alter table statements to add further columns to the table, which can then be renamed again. Alternatively, you could SELECT...INTO a new table to add a column, drop the old table and rename the new one back to the old name.
There's a bit of tricking around for specifying the datatypes for the columns: for types which have a length or precision, you'll need to create a datatype first, and rename it after you've created the column. Also, these columns will have to allow NULL values because they're added with alter table.
In the same fashion, you can create a datatype, then add a column of this type to a table, and then rename the datatype. The below stored procedure sp_rv_create_table uses this heavily.

Here you can download a stored procedure named sp_rv_create_table which does all of the above by creating a table according to the parameters you specify, for example:
exec sp_rv_create_table
        "mytable", 
        "ColOne", "int", 
        "ColTwo", "char(80)",
        "ColThree", "datetime"
The above statement will create a table named mytable, with columns ColOne, ColTwo and ColThree with the specified datatypes. In addition, the table will always have one column named dummycol of datatype timestamp. See the header of the downloaded file for additional deatils.



Dropping a variable table
Dropping a table with a variable name works the same way as creating one:
exec sp_rename "the_table_name",
               "some_unused_name" 

drop table some_unused_name 
You can also drop other types of object (such as views or procedures) this way. In fact, you can download a procedure named sp_rv_dropobject which basically drops any type of object. sp_rv_dropobject is part of a larger set of procedures which can be downloaded here.



Update statistics on a variable table
Updating the distribution statistics of a table basically works the same, but goes one step further and renames the table back to its original name after completing the operation:
exec sp_rename "the_table_name",
               "some_unused_name" 

update statistics some_unused_table

exec sp_rename "some_unused_name",
            "the_table_name"
Note that there is a possibly major disadvantage here: you shouldn't do this when there is a chance that any query would access the table under its original name while it's renamed. Also, you'll want to ensure the table is always renamed back. These problems make this a rather dangerous (yet interesting) way of playing with your tables. You shouldn't probably do this in a production environment.

Note that you can also use the stored procedure sp_rv_update_statistics , which probably does what you want. You can download this procedure (plus a few others) from here; details about these stored procedures are documented here.



Grant/revoke permissions on a variable table
In the same fashion as above, you can perform grant and revoke statements on a table (or other types of object) effectively having a variable table name:
exec sp_rename "the_table_name",
               "some_unused_name" 

grant select on some_unused_table to public

exec sp_rename "some_unused_name",
               "the_table_name"
The only time at which it's probably safe to do something like this is directly after the table has been created, so that there's no user accessing it yet.



Executing a variable stored procedure
When executing a stored procedure, you can specify the name of the procedure in a variable. This is actually an undocumented feature which was introduced in System 10 (so it won't work in version 4.x).
Two examples:
declare @procname varchar(80)

select @procname = "sp_helpdb"
exec @procname tempdb 
            
select @procname = "SYB_BACKUP...sp_who"
exec @procname 
            


Some possible practical applications
These are some further ideas for practical applications of the "rename object" tricks described above (don't forget the consider the potential risks caused by renaming objects around):
  • In a procedure or trigger, you cannot drop an object and recreate an object with the same name. The usual solution is to drop or recreate the object in a seperate procedure, but this isn't necessary: you can first rename the object to some unused name, then drop it using that same name, and then recreate it under its original name.
  • When a stored procedure should generate a table containing some result data, you could allow the user to specify the name of the result table as a parameter. You can then create a table in (for example) tempdb with a fixed name, and then rename this table to the name specified by the user. In fact, sp_rv_findobject uses exactly this principle to implement the OUTPUT= option.
  • Lastly, the above-mentioned procedure sp_rv_dropobject also uses some of these tricks to drop an object of any type.





 This document is located at www.sypron.nl/dynsql.html

 Copyright © 1998-2014 Rob Verschoor/Sypron B.V. 
 All rights reserved. All trademarks are acknowledged.