asked on

Pass multiple comma delimited values to subreport procedure

I have a sub-report with a procedure that has an id (string) parameter.
It can take multiple ids which are comma delimited ie ‘123,346,789’
If I run the sub report as its own report with the id = ‘123,346,789’ it returns the three values I need

If I pass one id (123) to the sub report from the main report it works fine.

However if I try to return more than one value (‘123,346’) to the sub report, it blows up with the error
“Function stp_ReturnID has too many arguments specified”

How do I get it the subreport to recognize the multiple comma delimited values?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fundsf

ASKER

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Alter procedure [dbo].[stp_ReturnID]
            @FundIds                  varchar(8000),
            @FromDate                  datetime,
            @ToDate                        datetime,
            @IsAnnualized            bit = 0
as
begin

      create table #key_unstrung (
            key_id            int
            )

      exec stp_ParseIdList @FundIds

      select      key_id as FundId,
                  f.FundName,
                  dbo.udf_ReturnDateRange( f.FundId, @FromDate, @ToDate, @IsAnnualized ) as [Return]
      from      #key_unstrung k with (nolock)
            join Funds f with (nolock) on f.FundId = k.key_id
      order by f.FundName

end

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

is FundID in table also string?

HainKurt

also post the code for stp_ParseIdList

HainKurt

you may use

select key_id as FundId,
f.FundName,
dbo.udf_ReturnDateRange( f.FundId, @FromDate, @ToDate, @IsAnnualized ) as [Return]
from Funds f
where ',' + @FundIds + ',' like '%,'+f.FundId+',%' like
order by f.FundName

and remove following code

create table #key_unstrung (
key_id int
)

exec stp_ParseIdList @FundIds

HainKurt

oops, try this and remove previous code in your sp

select f.FundId,
       f.FundName,
       dbo.udf_ReturnDateRange( f.FundId, @FromDate, @ToDate, @IsAnnualized ) as [Return]
  from Funds f 
 where ',' + @FundIds + ',' like '%,'+f.FundId+',%' like 
 order by f.FundName

Open in new window

fundsf

ASKER

I need to pass a formula/array to the stp.
Right now I have a a place holder '123,456,789'

If I pass the '123,456,789' straight to the stp parameter it works ,but not the formula place holder.

What it really needs to do is look at a pre-defined formula
if {?Index} = 'ABC' then 123
else
if {?Index} = 'DEF' then 345
else
if {?Index} = 'GHI' then 789.
Then build an comma delimited array based on that and pass it to the stp
fyi, The fundid is a number

fundsf

ASKER

I had to get rid of the second like to modify the proc.

Now I get this error
Conversion failed when converting the varchar value '%,' to data type int

select f.FundId,
       f.FundName,
       dbo.udf_ReturnDateRange( f.FundId, @FromDate, @ToDate, @IsAnnualized ) as [Return]
  from Funds f 
 where ',' + @FundIds + ',' like '%,'+f.FundId+',%'
 order by f.FundName

Open in new window

HainKurt

where ',' + @FundIds + ',' like '%,'+f.FundId+',%'

-->

where ',' + @FundIds + ',' like '%,' + cast(f.FundId as varchar) + ',%'

fundsf

ASKER

We ended up writing a different stp so I didn't have to pass the multiple ids