Given the following sql:-

create procedure sp2 (in @param integer)
begin
declare @sql varchar(100);
if @param = 1 then
set @sql = 'select 1 as a '
else
set @sql = 'select 2 as b, 3 as c ' //ATTEMPT 1
//ATTEMPT 2 set @sql = 'select 4 as b'
end if;
execute immediate with results on @sql
end

On execution, one gets the following results:-
call sp(1) //works, get column labelled "a" and 1
call sp(2) //fails

Revise the sql, commenting out ATTEMPT 1 and enabling ATTEMPT2
call sp(1) // works
call sp(2) // works, BUT the column name is "a" not b.

Now, what I'm wanting to do is change the result set... I know that
if I have some sort of trigger to call a different result set, I can
have 2 different results - but the column titles still don't change.

I've tried this in SP and TRANSACT dialects; I've tried putting the
values into a temp result set; I've tried deleting the #temp table ...

How do I get this to work so that I can vary the number of columns and
their labels?

Is this ASA syntax ?




eahofer4groupposting@gmail.com wrote:
> Given the following sql:-
>
> create procedure sp2 (in @param integer)
> begin
> declare @sql varchar(100);
> if @param = 1 then
> set @sql = 'select 1 as a '
> else
> set @sql = 'select 2 as b, 3 as c ' //ATTEMPT 1
> //ATTEMPT 2 set @sql = 'select 4 as b'
> end if;
> execute immediate with results on @sql
> end
>
> On execution, one gets the following results:-
> call sp(1) //works, get column labelled "a" and 1
> call sp(2) //fails
>
> Revise the sql, commenting out ATTEMPT 1 and enabling ATTEMPT2
> call sp(1) // works
> call sp(2) // works, BUT the column name is "a" not b.
>
> Now, what I'm wanting to do is change the result set... I know that
> if I have some sort of trigger to call a different result set, I can
> have 2 different results - but the column titles still don't change.
>
> I've tried this in SP and TRANSACT dialects; I've tried putting the
> values into a temp result set; I've tried deleting the #temp table ...
>
> How do I get this to work so that I can vary the number of columns and
> their labels?