SQL Server Performance Forum – Threads Archive

Dynamic Query problem

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 !

---

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

---

Great ! I didn’t know you could do it that way. works great.
thanks Jasper!

---

Ok, call me stupid but I don’t see what was removed from Dougwood’s query string to Jasper’s replacement… <sigh>

---

]]>