 Sybase Tips & Tricks by Rob Verschoor | |||
|
|
|
|||||||||||||||||||||||
|
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:
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):
|
|||||||||||||||||||||||
print version
|