problem with split function to select multiple values

When running the split function from my data pane within ssrs, or when
running it from sql server directly everything works fine.
But..when running the report in preview I receive dbo.split too many
arguments specified. I assume reporting services in some way passes it
like a string value once running the report. Otherwise it's also
noticeable the join function works fine within

code i'm using is like this within my dataset

Select myfield
from table
where field in
(
select item
from dbo.split(@par,',')
)

The parameter passed to the split function has an 'integer value'
specified for the 'parameter value' . The split value is the well
known generic split function.

Branden
Mon Feb 04 09:41:01 CST 2008

If @Par is a multivalued parameter, then try joining the items together in a
string:

Select myfield
from table
where field in
(
select item
from dbo.split(join(@par,','),',')
)






"koopmans.johan@hccnet.nl" wrote:

> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
>
> code i'm using is like this within my dataset
>
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@par,',')
> )
>
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>

Branden
Mon Feb 04 09:45:25 CST 2008

Update to my previous post, the parameter to the split function must be a
varchar parameter as this will be sent through as a single comma separated
string list to the function.

"koopmans.johan@hccnet.nl" wrote:

> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
>
> code i'm using is like this within my dataset
>
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@par,',')
> )
>
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>

nickismyname
Mon Feb 04 16:46:58 CST 2008

Branden, sorry for taking your time...I've been using a direct query
within SSRS so there was no need for the split (this explains the
error). I tend to use SP's or cubes regularly and confused the way
they handle parameters with a direct query i'm using now. So this is
the simple solution

Select myfield
from table
where field in
(
@par
)