SybaseBlog

Here is an SQL trick for beginners.

Say you want to know the columns in a table. You can run sp_help on that table, but here is another trick:

1> select * from tablename where 1=2
2> go

This will come back with all the columns and no matching rows (because 1=2 is always false). It will also come back very quickly because the optimizer knows there shouldn't be any matching rows.

This can be especially handy in a complex query that joins a lot of tables. In those cases, sp_help either wouldn't help you, or would take several steps to be pieced together.

The other day I was asked what happens when you provide a string that is too long to a table or stored procedure parameter. Rather than guess, that sounds like an easy thing to test.

create table atable (stringvar varchar(5))
go

insert into atable values ("too long")
go

select * from atable
go

stringvar 
--------- 
too l 

So it would appear as if the string is silently truncated. Step two is to double-check the behaviour in the documentation. This time we can verify this behaviour by looking at the Reference Manual Volume 1: Building Blocks, Chapter 1: System and User-Defined Datatypes (Character datatypes), which states:

"Entries shorter than the assigned length are blank-padded; entires longer than the assigned length are truncated without warning, unless the string_rtruncation option to the set command is set to on."

http://download.sybase.com/pdfdocs/asg1250e/refman1.pdf

Bear in mind that standard ANSI/ISO SQL/92 states that these values should NOT be silently truncated, it should reject the value! Once again this is a default setting that is for your convenience, but actually makes your database less ANSI-compliant. We've seen this once before with regards to the treatment of NULLs.

http://sybasease.blogspot.com/2005/05/nulls-in-sybase-ase.html

So let's try it with the setting where it should be. Remember this will only affect the current session.

set string_rtruncation on

insert into atable values ("too long")
go

Server Message:  Number  9502, Severity  16
Server 'TEST_SYBASE', Line 1:
Data exception - string data right truncated 

select * from atable
go

stringvar 
--------- 
too l

By the way, reading a little further in the documentation reveals this interesting note:
"Fixed-length columns tend to take more storage space than variable-length columns, but are access somewhat faster."

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.