SybaseBlog

SQL is kind of a misnomer. "Structured Query Language" is used for more than just queries.

Often, SQL is broken up in DDL, DCL and DML. DML, Data Manipulation Language, includes queries (SELECT) but also includes manipulation, like inserts and updates.

DDL is Data Definition Language, and includes modifying the structures of tables and so on.

Which brings us to the question I received. Is it possible to alter tables (perform DDL), say to add a column, inside a stored procedure? And then insert/select that column?

The short answer is yes.

First, make sure that you have the correct database options configured. Run sp_helpdb to show which options are "on". If it does not say so explicitly, the DBA should run the following command:

sp_dboption 'db_name', 'ddl in tran', true

More information on this is available in Chapter 22 of the System Administration Guide.

Now let us explore the "how" of using DDL in transactions.

Here is a sample table for our test:

create table a_table (a_col int)
go

create procedure a_proc
as begin
alter table a_table add b_col int default 1
end

Let's make an observation here: I'm going to put the DDL statement in its own transaction. That's generally a good practise, and sometimes mandatory. For our test, we can call this procedure from another procedure.

create procedure b_proc
as begin
exec a_proc
select a_col, b_col from a_table
end

This won't work. Why? Because b_col does not exist at the time of compilation.

We should feel fortunate we are getting a compile-time error instead of a run-time error, which is what we would get if we were, say, removing a column.

So you might think that we should do this instead, to avoid compile-time and run-time errors:

create procedure b_proc
as begin
exec a_proc
select * from a_table
end

This will avoid the compile-time error, but it won't work. The '*' is being interpreted at compile-time, so this will simply ignore the new column we added. And we would still have the run-time error if we removed a column.

Instead we need to use "dynamic SQL". The column will be there at run-time, just not at compile-time.

http://sybasease.blogspot.com/2005/05/dynamic-sql.html

create procedure b_proc
as begin
exec a_proc
exec ('select a_col, b_col from atable')
end

That will work.

Please note the obvious: a real-life application should have proper error-checking and handling, unlike these rough, simple examples.

In Summary:
1. Use sp_dboption to set 'ddl in tran' to true.
2. Put your DDL operations in a separate stored procedure which you can from your main stored procedure.
3. Use dynamic SQL for any DML operations on affected tables.