Has someone any idea how can I write this complex Query in sql-anywhere?

[Baron]

For some reason, I need a query that builds all the input parameters of a given procedure along with their values in one string, the query should work with any procedure regardless of the number of the input parameters.

For example I have such a procedure:

create or replace procedure mytest (par1 varchar(10) , par2 varchar(10), par3 int)
result (ss long varchar)
begin
select 'how can i write this?'
end;

And my call looks like this:

select * from mytest('abc', null, 3)

Then I expect the result like this:

par1='abc', par2=null, par3=3

[Baron]

The table sysprocparm helps me finding the names of each parameter for the current proceudre, however I need once the name of the parameter and once its value.

It looks like I need "execute immediate" inside another "execute immediate" which ends with syntax error.

[chris_keating]

Here is a rough idea of an implementation.  You may want to handle domains with more detail - I have simply single quoted values whose domains that are character based.

create or replace procedure mytest (in par1 varchar(10) , in par2 varchar(10), in par3 int)
    result (ss long varchar)
begin
    declare @wip long varchar;

    -- build a list of parms with placeholders for input arguments
    select list(
        string(
            parm_name, '=',
            -- single quote character domains
            if( domain_id in (6,7,8,9,10,13,32,33,34,35,36)) then
                ''''
            else
                ''
            end if,
            -- construct a substute placeholder :<parm_id>
            ':', 
            parm_id, 
            -- single quote character domains  
            if( domain_id in (6,7,8,9,10,13,32,33,34,35,36)) then
                ''''
            else
                ''
            end if
    
        ), ', ' order by parm_id) into @wip
    from sysprocparm 
    where 
           proc_id = @@procid 
       and parm_mode_in = 'y';

    begin
        declare @parmid integer;
        declare @parmname char(128);
        declare @parmval long varchar;

        declare parms cursor for
            select parm_id, parm_name from sysprocparm
                where             
                      proc_id = @@procid 
                  and parm_mode_in = 'y'
                order by parm_id;
        open parms with hold;
        loop
            fetch next parms into @parmid, @parmname;
            if sqlcode <> 0 then leave endif;
            -- get the parameter value, handling any NULL parms
            execute immediate 'select cast(' || @parmname || ' as long varchar) into @parmval';
        -- replace placeholder with value
        if @parmval is null then
            set @parmval = 'null';
            set @wip = replace(@wip, string(''':',@parmid,''''), @parmval);    
        else
            set @wip = replace(@wip, string(':',@parmid), @parmval); 
        end if;
        end loop;        
        close parms;
    end;
    
    select @wip;
end;

call mytest('abc',null,3);

[Baron]

Thanks @chris_keating for the samrt reply.

My goal was to build a mechanism for logging all procedure calls, I got your script and adapted it to my case.