Hi everyone,

I'm having a problem with an IQ 12.5 stored procedure.

I need to create dynamic sql statements to run against the db because
my user can select any of a group of metrics for a report, so I only
want to generate the metrics they request.

Since the execute statement will not allow me to return a multirow
resultset, I dump the results into a temporary table, select from it
and then drop the temp table. BTW - is there any other statement that
WILL let me return a multirow resultset?

I've setup my sp and it works fine the first time it's run. But on
subsequent runs, I get an error because it is looking for metrics from
the first run. It appears that something is staying in memory/cached.

Can you help? Any ideas or hints are appreciated.
Thanks,
Patti

Here is a simplified example of the data, sp and the statements used
to run them.

STATEMENTS USED
--run this first
execute thisisatest @met = 'wab,sab', @firstcolumn = 'dte'
--run this second
execute thisisatest @met = 'wab,sac', @firstcolumn = 'prd'
--parameter @met is the metrics being requested
--parameter @firstcolumn is used to group the data. In this example,
users can see numbers by date (dte) or product (prd)

TABLE
create table abc(as_of_dt timestamp,product char(3),aaa integer, bbb
integer, ccc decimal(12,2))
insert into abc values('2003-09-01','red',2,9,3.4)
insert into abc values('2003-10-01','red',1,8,1)
insert into abc values('2003-11-01','red',4,2,6)
insert into abc values('2003-10-01','blk',9,8,7.6)
insert into abc values('2003-11-01','blk',10,23,55)

SP
create procedure thisisatest @met varchar(1000),@firstcolumn char(3)
as

declare @selects varchar(10000), @groupby varchar(100)

--this field "wab" will always be selected
if @firstcolumn = 'dte' select @selects = @selects + '''i''+
dateformat(as_of_dt,''mm/yy'') as wab', @groupby = 'as_of_dt'
if @firstcolumn = 'prd' select @selects = @selects + '''i''+ product
as wab', @groupby = 'product'

--select metrics to display. These may vary
if charindex('sab',@met) > 0 select @selects = @selects + ',''$'' +
convert(varchar(30),SUM(aaa)) as sab'
if charindex('sac',@met) > 0 select @selects = @selects + ',''$'' +
convert(varchar(30),SUM(bbb)) as sac'
if charindex('sad',@met) > 0 select @selects = @selects + ',''$'' +
convert(varchar(30),SUM(bbb)/SUM(aaa)) as sad'

execute('create table #tmpinfo (' + replace(@met,',',' varchar(32),')
+ ' varchar(32))')

execute(
'insert into #tmpinfo select ' + @selects + '
from abc group by ' + @groupby + ' order by wab ')

select * from #tmpinfo
drop table #tmpinfo
return