Passing parameter to subquery

Question

Now this one is driving me mad. It gets as simple as trying to add a new query to my QueriesTableAdapter (typed DataSet) and assigning it a query that takes a parameter in the SUBQUERY; something like:

SELECT CONVERT(bit, CASE WHEN (SELECT ROUND(SUM(Amount), 2) FROM  [Transaction]
       WHERE UserID = @UserID) >= 9.99 THEN 1 ELSE 0 END)

The wizard returns must declare scalar variable @UserID.

I've been working with ADO.NET for over 8 years now, but haven't noticed this problem before. I have recently upgraded to SQL Server 2012 Express; maybe something to do with that?

MORE INFO

I just resolved the problem at hand (like all good devs) by removing the subquery, like this:

SELECT CONVERT(bit, CASE WHEN ROUND(SUM(Amount), 2) >= 9.99 THEN 1 ELSE 0 END) 
    FROM [Transaction] WHERE (UserID = @UserID)

but this doesn't address the actual concern, passing a param to subquery. I further found that I can't use a parameter even in CASE clause. For example, if I'd rather avoid hardcoding the price (9.99) in the query and replace it with a varible, but doing so raises the same error as above.

Answer 1

Looks like I found a workaround. It is not pretty but it works. I'd welcome anything that is better than this and doesn't need Stored Procs.

It appears that TableAdapter reads query parameters from the WHERE clause only (probably to infer their types), so if you could introduce your variable in WHERE too, using an always-true condition of course, it would work. Something like:

SELECT CONVERT(bit, CASE WHEN ROUND(SUM(Amount), 2) >= @Price THEN 1 ELSE 0 END) 
    FROM [Transaction] WHERE UserID = @UserID AND (Amount <= @Price OR Amount > @Price)

That last part of the WHERE clause is there to simply introduce the @Price variable and to define its type and has no effect on results. Hopefully SQL Server query optimizers will truncate this part before executing the query.