SQL Server Performance

Dynamic Query problem

Discussion in 'SQL Server Reporting Services' started by dougwood, Feb 10, 2004.

  1. dougwood New Member

    I am trying to create a dynamic query and add a 'ALL' category to my dropdown list, exactly like the functionality in the BOL walkthrough.
    However I keep getting the "[BC30201]Expression Expected" error.

    My query BEFORE attemting the dynamic query looks like this (much of the query is omitted)


    ....
    AND t567.c536870970 = masterSr
    AND t567.c536870970 in (SELECT c536870923 from t566 where (c536870922 = ?))
    order by msrid

    This works fine before making the query dynamic.

    I then add 'All' to my dropdown parameter (Parameter1) like below:


    SELECT 'All' AS Sponsor FROM t558
    UNION
    SELECT c536870917 AS Sponsor FROM t558 GROUP BY c536870917

    The dropdown now has 'All' added to it. no problems.
    But when I try to make the query dynamic, I get errors.
    This is the new query:


    ="SELECT .....
    AND t567.c536870970 = masterSr AND t567.c536870970 in (SELECT c536870923 from t566" & IIF(Parameters!Parameter1.Value = 'All', ""," where (c536870922 = " & Parameters!Parameter1.Value & "))") & " order by msrid"

    As the BOL says, I have taken all carriage returns and spaces out and I'm still getting this error.
    I am wondering if anyone sees anything that I am doing wrong, and better yet, Is there a better/easier way to do dynamic querying than this ?

    Thanks for any help !
  2. jasper_smith New Member

    Does this work

    ="SELECT .....
    AND t567.c536870970 = masterSr AND t567.c536870970 in
    (SELECT c536870923 from t566" &
    IIF(Parameters!Parameter1.Value = "All",
    ")",
    " where c536870922 = ?)") &
    " order by msrid"

    Remove the linebreaks, I just though they might make the bits standout better

    HTH

    Jasper Smith
  3. dougwood New Member

    Great ! I didn't know you could do it that way. works great.
    thanks Jasper!
  4. BrenBart New Member

    Ok, call me stupid but I don't see what was removed from Dougwood's query string to Jasper's replacement...

    <sigh>

Share This Page