Adaptive Server automatically replaces asterisks in queries with actual column names before saving new stored procedures, views, and triggers. This replacement persists even if you alter a table to add columns. Adaptive Server does not allow more than one column, however, when the replacement of the asterisk introduces an additional column. This incorrect behavior persists until you drop and re-create the text. For example:
1> create table t1(c1 int, c2 int) 2> go 1> create table t2(c1 int) 2> go 1> create proc p1 2> as 3> select * from t1 where c1 in (select * from t2) 4> go 1> exec p1 2> go c1 c2 ----------- ----------- (0 rows affected) (return status = 0) 1> sp_helptext p1 2> go # Lines of Text --------------- 2 (1 row affected) text ------------------------------------------------------------------------------------------------------------ create proc p1 as/* Adaptive Server has expanded all '*' elements in the following statement */ select t1.c1, t1.c2 from t1 where c1 in (select t2.c1 from t2) (2 rows affected) (return status = 0) 1> alter table t2 add c2 int null 2> go 1> exec p1 2> go c1 c2 ----------- ----------- (0 rows affected) (return status = 0) 1> exec p1 with recompile 2> go c1 c2 ----------- ----------- (0 rows affected) (return status = 0) 1> drop proc p1 2> go 1> create proc p1 2> as 3> select * from t1 where c1 in (select * from t2) 4> go Msg 299, Level 16, State 1: Procedure 'p1', Line 4: The symbol '*' can only be used for a non-EXISTS subquery select list when the subquery is on a single table with a single column.
Adaptive Server expects the asterisk to resolve to a single column, and generates an error when it encounters more than one column after it converts the asterisk.
